|
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 command is:
COPY FROM
database TO
database action
-
destination_table
(column_name,
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 scott/tiger@ORCL92
to scott/tiger@ORCL92-
create new_emp using
select * from emp;
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
copycommit 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 LONG
datatype.
copy
script download
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:
create new_emp (col1,
col2, …) –
A 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 destination.
The
following command copied
the
table_with_one_million_rows
table to new_table:
|
