The SQL*Plus COPY command can copy data between two databases via SQL*Net.
Syntax:
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.
Sample:
@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; A ---------- 1
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.