Tuesday, January 18, 2011

Table Copy From One oracle Database to Another oracle database


Table Copy From One oracle Database to Another oracle database
==============================================================

using SQL*Plus COPY Command. (not dblink). It is very easy.
you have to TNS configure of that two remote database only .

[ The COPY command will be obsoleted in future releases of SQL*Plus.
COPY supports the datatypes listed for the COPY command,
but no new datatypes will be supported. ]


Copies data from a query to a table in a local or remote database.
COPY supports the following datatypes:

CHAR
DATE
LONG
NUMBER
VARCHAR2



COPY Command Syntax
-------------------

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query



sqlplus copy syntax help :-
------------------------

SQL> copy help=y

usage: COPY FROM TO { () } USING
: database string, e.g., hr/your_password@d:chicago-mktg
: ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
: name of the destination table
: a comma-separated list of destination column aliases
: any valid SQL SELECT statement

SQL>


example:-
---------


SQL> copy from scott/tiger@juncls to scott/tiger@test107
create empback using select * from dept ;


Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table EMPBACK created.

4 rows selected from scott@juncls.
4 rows inserted into EMPBACK.
4 rows committed into EMPBACK at scott@test107.

SQL>



Specifying Another User's Table
=================================

SQL> copy from scott/tiger@juncls to scott/tiger@test107
create empback using select * from hr.departments ;
====




Copying Data between Tables on One Database
============================================

You can copy data from one table to another in a single database (local or remote).
To copy between tables in your local database, specify your own username and password
and the service name for your local database in either a FROM or a TO clause (omit the other clause):

COPY FROM HR/your_password@MYDATABASE -
INSERT EMPLOYEE_COPY2 -
USING SELECT * FROM EMPLOYEE_COPY

To copy between tables on a remote database, include the same username, password, and
service name in the FROM and TO clauses:

COPY FROM HR/your_password@BOSTONDB -
TO HR/your_password@BOSTONDB -
INSERT EMPLOYEE_COPY2 -
USING SELECT * FROM EMPLOYEE_COPY

1 comment:

Anonymous said...

thnkx buddy ..........ll contact u in futureeeeeeeeeee

My site statCount