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 inadvertandly 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 rumours 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.
This tip on the COPY command came to light when looking through a new paper called "Oracle Untapped Utilities" by Dave Moore. It's got sections on the COPY command, plus many other such as DBMS_XPLAIN, and it's well worth taking a look.
7:16:03 PM 
|
