The SQL*Plus COPY command can copy data between two databases via SQL*Net. The preferred method of doing this is to use SQL*Plus on the host where the database resides. If performing the copy command from a client SQL*Net connection, the data is transferred through the client machine.
The SQL*Plus COPY command can copy data between two databases via SQL*Net. The preferred method of doing this is to use SQL*Plus on the host where the database resides. If performing the copy command from a client SQL*Net connection, the data is transferred through the client machine.
The copy command copies data from one Oracle instance to another. The data is simply copied directly from a source to a target. The format of the copy command is:
COPY FROM database TO database action -
destination_table (column_name, column_name...) USING query
The action can include:
· create – If the destination table already exists, copy will report an error, otherwise the table is created and the data is copied.
· replace – If the destination table exists, copy will drop and recreate the table with the newly copied data. Otherwise, it will create the table and populate it with the data.
· insert – If the destination table exists, copy inserts the new rows into the table. Otherwise, copy reports an error and aborts.
· append– Inserts the data into the table if it exists, otherwise it will create the table and then insert the data.
eg:
SQL> help copy
COPY
----
COPY copies data from a query to a table in a local or remote
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
SQL> copy from USER/PASS@DB to USER/PASS@DB2 -
> insert table_name using select * from table_name;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
10 rows selected from USER@DB
10 rows inserted into table_name .
10 rows committed into table_name at USER@DB2
HuiYi_love
