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:
thnkx buddy ..........ll contact u in futureeeeeeeeeee
Post a Comment