|
NOTE:
MOSC Note 171870.1 says
that the COPY command is
deprecated as of Oracle
10g, use data pump instead
. .
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.
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.
COPYing Data Using SQL*Plus
SQL Plus has
a useful command called COPY that until now I had no idea about. It's
an alternative to the IMP and EXP commands that lets you copy data
between two SQL*Net connected databases.
Using the
syntax
COPY FROM database TO database action -
destination_table (column_name, column_name...) USING query
You can quickly copy data from one
database instance to another using an SQL query, that lets you CREATE
a new table, REPLACE an existing table, INSERT values to an existing
table or APPEND values to an existing table.
A typical
example of the COPY command in use would be;
SQL> copy from scott/tiger @ORCL92 -
to scott/tiger @ORCL92-
create new_emp ?
using select * from emp;
which would
create a new table called NEW_EMP on the same database, copying across
all the data from the EMP table. It could equally be to a different
database, and you could limit down the data copied using a WHERE
clause. You can SET three SQL*Plus parameters, ARRAYSIZE, COPYCOMMIT
and LONG, which determine the number of rows that SQL*Plus will
retrieve at one time, the number of retrieves that will take place
before a COMMIT is issued, and the number of characters copied when a
column is a LONG datatype.
If you're
moving data from one remote server to another, it's best to run the
command directly from either of the servers, as if you run it at your
client machine, the data passes through your PC on route between the
two servers.
You can also
run this command using the iSQL*Plus web-based version of SQL*Plus,
which would make it easier to avoid inadvertently moving the data
through your client machine, as you'll be able to directly log on to
the remote server rather than running the command locally. Also,
there's rumors that SQL*Plus will either be desupported, or even not
shipped, with Oracle 10G, so it's probably worth getting used to
running it on the web-based version from the outset.
|
and include the URL for the page.
