Oracle Utilities Tips
The SQL*Plus copy Utility
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
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.
SQL> copy from
using select * from
Once the command above is executed, the
copy utility displays the values of three parameters, each
of which can be set with the SQL*Plus set command. The
arraysize specifies the number of rows that SQL*Plus
will retrieve from the database at one time. The
parameter specifies how
often a commit is performed and is related to the
number of trips one trip is the number of rows defined in
arraysize. Finally, the long parameter displays
the maximum number of characters copied for each column with a
fetch/bind size is 15. (arraysize is 15)
commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
1400 rows selected from scott@ORCL92.
1400 rows inserted into NEW_EMP.
1400 rows committed into NEW_EMP at scott@ORCL92.
SQL> desc new_emp;
Name Null? Type
NOT NULL NUMBER(4)
The command above did not specify column
names for the new table (new_emp). As a result, the
new table will have the same column names as the table being
copied. If different column names are required, they can be
specified after the table name:
new_emp (col1, col2,
However, if one column name is specified,
they all must be specified.
A Remote DBA could perform this same function
with a database link from one database pointing to another.
The appeal of the copy command is that it only requires
SQL*Net service names and proper privileges to get the job
done. For those environments that restrict the usage of
database links, the copy utility can be leveraged. In
addition, the copy command provides many options, as
defined by the actions create, replace, insert and append.
If the copy command is executed
from a client PC to copy data from remote database DB0 to
remote database DB1, the data will be copied from DB0 to the
client PC and then to DB1. For this reason, it is best to use
SQL*Plus from either remote host and not require the data to
travel through a client machine in order to reach its final
The following command copied the
table_with_one_million_rows table to new_table:
To learn more about these techniques, see
the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code depot
of utilities scripts.