Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle SQL*Plus Copy Command

Oracle Tips by Burleson Consulting
Don Burleson


The SQL*Plus copy Utility 

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.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.