Using SQL*Plus Copy Command to move data

SQL*Plus Copy Command usage:
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, …)]
USING query

Example:

SQL> copy from scott/tiger@orcl to scott/tiger@orcl create dept1 using select * from dept;

–You can ommit the from or to statement if the destination is as as the source database.

SQL> copy from scott/tiger@orcl create dept2 using select * from dept;

SQL> copy to scott/tiger@orcl create dept3 using select * from dept;

Tips: You can set the arraysize to 5000, the maximum, to improve the performance

SQL> set arraysize 5000

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

3 Responses to Using SQL*Plus Copy Command to move data

  1. Daniel says:

    Hi I want to know how can I use the copy command between Oracle and SQL Server, I want copy data from a table in SQL Server to another table in Oracle and I want to use the SQL Plus Copy command.

    Thank you.

  2. alexzeng says:

    Sql*Plus Copy command can only used between oracle databases. For your purpose, Sql Server DTS tool can do that. Or you can create db link between Sql Server and Oracle, see https://alexzeng.wordpress.com/2008/08/08/how-to-connect-oracle-and-sql-server/
    Regards,
    -Alex

  3. neworacledba says:

    this is an informative command

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: