SQL中copy命令使用

Oracle SQL中可以使用copy命令来进行数据的复制.
只需要知道源端和目标端数据库的sqlplus登陆方式,就可以进行表中数据的复制,相对来说也比较灵活.

如下,我们知道两个数据库的sqlplus登陆方式
sqlplus UATDB7/UATDB7@UAT2
sqlplus charsi/charsi@BMCTST9I

现在我们要将UATDB7/UATDB7@UAT2中一部分all_objects表中的数据insert到charsi/charsi@BMCTST9I数据库中的test_tbl表中.可以使用下面的命令

SQL> copy from UATDB7/UATDB7@UAT2 to charsi/charsi@BMCTST9I insert test_tbl using select * from all_objects where rownum <1001;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
1000 rows selected from UATDB7@UAT2.
1000 rows inserted into TEST_TBL.
1000 rows committed into TEST_TBL at charsi@BMCTST9I.

SQL> select count(1) from test_tbl;

COUNT(1)
----------
1000

在执行前,为了保证效率和防止回滚段不足的情况,可以设置copycommit参数,如下:

SQL> set timing on echo on;
SQL> set arraysize 50
SQL>
SQL> set copycommit 100
SQL> copy from UATDB7/UATDB7@UAT2 to charsi/charsi@BMCTST9I insert test_tbl using select * from all_objects where rownum <1001;

Array fetch/bind size is 50. (arraysize is 50)
Will commit after every 100 array binds. (copycommit is 100)
Maximum long size is 80. (long is 80)
1000 rows selected from UATDB7@UAT2.
1000 rows inserted into TEST_TBL.
1000 rows committed into TEST_TBL at charsi@BMCTST9I.

SQL> select count(1) from test_tbl;

COUNT(1)
----------
2000

Elapsed: 00:00:00.00
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> select count(1) from test_tbl;

COUNT(1)
----------
2000

Elapsed: 00:00:00.00

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23850820/viewspace-1048563/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-1048563/