Using SQL*Plus Copy Command to move data

Posted on October 14, 2008 by alexzeng

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

Filed under: Data Management

« »

2 Responses

  1. Daniel, on January 22nd, 2009 at 5:33 pm Said:

    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.

    Reply
  2. alexzeng, on February 27th, 2009 at 6:53 am Said:

    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 http://alexzeng.wordpress.com/2008/08/08/how-to-connect-oracle-and-sql-server/
    Regards,
    -Alex

    Reply

Leave a Reply