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/，如需轉載，請註明出處，否則將追究法律責任。