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

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.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on February 22, 2008 at 1:38 pm and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.