SQL*Plus Copy

Many times we are faced this question of – what is fastest way to copy the data from one database to another. Let’s take 3 examples –

1. insert into roop.t@target select * from mark.t;

2. To make to more fast we can use –

insert /*+ append */ into roop.t@target select * from mark.t;

3. Lets look at the SQL*Plus Copy option. 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.

copy from mark/mark@source to roop/roop@target create t using select * from t;

Let’s understand the syntax –

FROM database - The database that contains the data to be copied. If you omit the FROM clause, the source defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must use a FROM clause to specify a source database other than the default. The COPY command FROM clause does not support SYSDBA or SYSOPER privileged connections.

TO database - The database containing the destination table. If you omit the TO clause, the destination defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must use a TO clause to specify a destination database other than the default. The COPY command TO clause does not support SYSDBA or SYSOPER privileged connections.

APPEND - Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.

CREATE - Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.

INSERT - Inserts the rows from query into destination_table. If destination_table does not exist, COPY returns an error. When using INSERT, the USING query must select one column for each column in destination_table.

REPLACE - Replaces destination_table and its contents with the rows from query. If destination_table does not exist, COPY creates it. Otherwise, COPY drops the existing table and replaces it with a table containing the copied data.



This command is no more enhanced since Oracle 8.0 and may be declared as obsolete in a future version (but is still there in 11gR2) but there is no replacement to easily copy tables with LONG column (LONG datatype is obsolete).

Since main foucs of this topic was SQL*Plus Copy, I didn't touched other ways to copy data from one database to another like export/datapump, External Tables, sql*Loader, etc.