Systems Engineering and RDBMS

COPY Command in SQL *PLUS to copy data across Oracle databases

Posted by decipherinfosys on February 22, 2008

The COPY command is a SQL *PLUS command and not SQL or PL/SQL. It is facilitated by the fact that SQL *PLUS can connect to different databases simultaneously. In one of our previous posts, we had covered database links as a means of copying over or querying data across databases. Another method to do so is using the COPY command. Here is an example:

SET ARRAYSIZE 50

SET LONG 1000

SET COPYCOMMIT 100 /* To set commit size */

COPY FROM USERA/USERA@SID1 TO USERB/USERB@SID2 INSERT TABLEA USING SELECT * FROM TABLEA where …;

Here, the USERA and USERB are two separate users on two separate databases and the copy command is being used to copy over the data in TABLEA from USERA on one database to USERB on another database. It assumes that the structure of the tables is exactly the same. One can specify the filter conditions that they want and whatever form or shape of query they want to specify when copying over the data from the remote database.

In 10g and above, one should make use of the data pump commands: expdp/impdp since COPY command has been deprecated.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: