Copy Table Data Across Databases/Schemas In Oracle

Sometimes you need to copy table across databases/schemas. Oracle provides some utilities for them. We can summarize them as follow:


Trasportable Tablespaces
IMPD/EXPD utilities(Import/Export data pump)
IMP/EXP(Import/Export)
SQL*Loader
CTAS(Create Table As Select) with dblinks
SQL*Plus COPY command


In my opinion, the easiest way is using the least famous one, the last one: SQL*Plus COPY command

I demonstrate a simple example to show how to copy table u1 schema to hr schema:



SQL> copy from u1/u1@XE to hr/hr@XE create ttttt using select * from t;

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 TTTTT created.

   9 rows selected from u1@XE.
   9 rows inserted into TTTTT.
   9 rows committed into TTTTT at hr@XE.

SQL> conn hr/hr@XE
Connected.
SQL> select * from ttttt;

         I
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

SQL>