SQL*Plus COPY command

Two days back we were investigating a discrepancy in parameters of ASCP Planning Data Pull concurrent program. The instance with Collection rollup 22 had 3 extra parameters and the instance with rollup 23 did not have these. We decided to compare the ad_bugs table of the two instances. There were more than 200,000 rows in ad_bugs. I thought of the SQL*Plus COPY command. Using this command I was able to copy the more than 200,000 rows in less than 5 seconds. Here's the command:

COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB -
CREATE EMP_COPY USING -
SELECT * -
FROM EMP;

The hyphens at the end of each line are needed to let sql*plus know that the command is not yet complete.

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. If you type copy on sql prompt without any parameters, it will give you the syntax.