Tuesday, November 15, 2016

Copy a table from one database to another database in Oracle


We can use any of following  method to copy one table from one database to another database.

1) using create table as select with help of DB_LINK between two databases

create table target_table_name as select * from source_table_name@dblink 

2) Using Legacy exp/imp utility

3) Using datapump expdp/impdp

4) Using SQLPlus copy command

5) Using other tools like SQL developer/Toad for Oracle etc.

Here I am giving only example of #4 (using SQLplus copy command). becuase this is looks like interesting to me.

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

example - (from any machine in your network)

SQL> copy from hr/hr@orcl to scott/tiger@orcl2 create employees_copy using select * from employees ;


Be carefull- SQLPlus COPY supports only the following datatypes:

    CHAR
    DATE
    LONG
    NUMBER
    VARCHAR2