SQL*Plus Copy

The SQL*Plus COPY command can copy data between two databases via SQL*Net.


USER1 @srcdb> copy
usage: COPY FROM [db] TO [db] [opt] [table] { ([cols]) } USING [sel]
[db]   : database string, e.g., hr/your_password@d:chicago-mktg
[opt]  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
[table]: name of the destination table
[cols] : a comma-separated list of destination column aliases
[sel]  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.

@Target database, the table initially does not exist.

USER1 @targetdb> select * from rep_tab;
select * from rep_tab
ERROR at line 1:ORA-00942: table or view does not exist

@Source database, run copy command within SQL Plus.

USER1 @srcdb> copy from user1/user1@srcdb -
> to user1/user1@target_host:1521/target_sid -
> create rep_tab -
> using -
> select * from rep_tab;

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

1 rows selected from user1@SRCDB.
1 rows inserted into REP_TAB.
1 rows committed into REP_TAB at user1@host1:1521/targetsid.

@Target database, now table is copied here.

USER1 @targetdb> select * from rep_tab;


This is a quick and easy way to copy data between two databases especially on where the usage of database link is restricted. It only requires SQL*Net service name (or host/port/service) and proper privileges.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top