Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation






     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 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.


Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table NEW_EMP created.


   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
 ----------------------------------------- -------- --------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

 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, …) –

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 destination.


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.




Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.