SQL*Plus copy 命令處理大批量資料複製

    對於資料庫表級上的資料複製，我們最常用的是CREATE TABLE AS(CTAS)..方式。其實在SQL*Plus下面copy命令可以完成同樣的工作，而且更加出色，效能也比較優異。更突出的是支援跨平台，異構資料庫之間的資料複製。copy命令可以類似地完成一些stream完成的功能，儘管copy命令與stream方式不是一個重量級。下面描述copy命令的主要用法。

 

1、copy命令的協助資訊

scott@SYBO2SZ> help copy COPY ---- Copies data from a query to a table in the same or another database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2. COPY {FROM database | TO database | FROM database TO database}            {APPEND|CREATE|INSERT|REPLACE} destination_table            [(column, column, column, ...)] USING query where database has the following syntax:     username[/password]@connect_identifier上面列出了copy支援的資料類型以及copy命令的用法from database 子句指定串連的來源資料庫，如果省略則為當前串連的資料庫to database子句指定串連的目的資料庫，如果省略則為當前資料庫from database TO database 同時指定了串連的原資料庫以及目的資料庫支援幾種不同的表間資料複製方式:APPEND|CREATE|INSERT|REPLACE支援跨Oracle版本，不同schema之間，相同schema之間的資料複製支援異構資料庫間的資料複製，如Oracle到非Oracle資料庫支援Oracle跨平台間的資料庫複寫，如windows平到到linux平台支援本機資料庫到遠端資料庫，遠端資料庫到本地，遠端資料庫到另一個遠端資料庫之間資料複製複製資料時，使用Oracle net來傳輸資料

2、同一資料庫相同schema之間資料複製

--create 方式，僅指定from子句--注，下面的樣本中,符號"-"表示是串連符號，用於換行書寫scott@SYBO2SZ> copy from scott/tiger@sybo2sz -> create tb_emp -> using select * from emp;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table TB_EMP created.   14 rows selected from scott@sybo2sz.   14 rows inserted into TB_EMP.   14 rows committed into TB_EMP at DEFAULT HOST connection.--append方式，僅指定to子句scott@SYBO2SZ> copy to scott/tiger@sybo2sz -> append tb_emp -> using select * from emp;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)   14 rows selected from DEFAULT HOST connection.   14 rows inserted into TB_EMP.   14 rows committed into TB_EMP at scott@sybo2sz.scott@SYBO2SZ> select count(*) from tb_emp;  COUNT(*)----------        28--insert 方式scott@SYBO2SZ> copy from scott/tiger@sybo2sz -> insert tb_emp2 using select * from emp where deptno=20;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)   5 rows selected from scott@sybo2sz.   5 rows inserted into TB_EMP2.   5 rows committed into TB_EMP2 at DEFAULT HOST connection.--replace方式，上一次實驗得到的表記錄數為5，使用replace後記錄數為14，如下,scott@SYBO2SZ> copy from scott/tiger@sybo2sz -> replace tb_emp2 using select * from emp;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table TB_EMP2 dropped.Table TB_EMP2 created.   14 rows selected from scott@sybo2sz.   14 rows inserted into TB_EMP2.   14 rows committed into TB_EMP2 at DEFAULT HOST connection.--使用列別名的方式--下面使用了列別名，且只複製其中的幾列資料scott@SYBO2SZ> copy from scott/tiger@sybo2sz -> replace tb_emp2(eno,name,job_name) using select empno,ename,job from emp;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table TB_EMP2 dropped.Table TB_EMP2 created.   14 rows selected from scott@sybo2sz.   14 rows inserted into TB_EMP2.   14 rows committed into TB_EMP2 at DEFAULT HOST connection.

3、同一資料庫不同schema之間資料複製

--下面使用了append方式，同時指定from及to子句scott@SYBO2SZ> copy from scott/tiger@sybo2sz to goex_admin/xxx@sybo2sz -> append tb_emp using select * from emp;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table TB_EMP created.   14 rows selected from scott@sybo2sz.   14 rows inserted into TB_EMP.   14 rows committed into TB_EMP at goex_admin@sybo2sz.

4、不同資料庫之間的資料複製

--不同資料庫之間的複製一定要指定目的資料庫連接字串       scott@SYBO2SZ> copy from scott/tiger@sybo2sz to goex_admin/xxx@cnmmbo -> append tb_emp using select * from emp;Array fetch/bind size is 2000. (arraysize is 2000)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table TB_EMP created.   14 rows selected from scott@sybo2sz.   14 rows inserted into TB_EMP.   14 rows committed into TB_EMP at goex_admin@cnmmbo.

5、不同oracle版本之間的資料複製

--下面是oracle 10g到oracle 11g之間的資料複製cott@SYBO2SZ> copy from scott/tiger@sybo2sz to scott/tiger@ora11g -> create tb_emp using select * from emp where deptno=30;Array fetch/bind size is 2000. (arraysize is 2000)Will commit after every 0 array binds. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table TB_EMP created.   6 rows selected from scott@sybo2sz.   6 rows inserted into TB_EMP.   6 rows committed into TB_EMP at scott@ora11g.--也可以從oracle 11g複製資料到oracle 10g，此處省略--跨平台複製資料，沒有環境，有待測試

6、copy命令的績效參數

與copy效能相關的幾個參數arraysize  該參數用於SQL*Plus 每一次fetch資料的行數，預設值為15，有效值是1到5000 copycommit 該參數用於copy完多少行資料之後執行commit，如果該值為0,則表示所有資料複製完畢後再執行commitlong       該參數用於設定long字元類型的最大長度，Oracle不建議使用long類型而是使用lob類型來取代--首先設定參數arraysize與copycommitscott@SYBO2SZ> set arraysize 15scott@SYBO2SZ> set copycommit 0--清空緩衝scott@SYBO2SZ> alter system flush buffer_cache;scott@SYBO2SZ> alter system flush shared_pool;--執行指令碼調用copy建立表scott@SYBO2SZ> @/users/robin/dba_scripts/custom/temp/cp_cmd.sqlPL/SQL procedure successfully completed.Array fetch/bind size is 15. (arraysize is 15)Will commit when done. (copycommit is 0)Maximum long size is 5000. (long is 5000)Table CP_BIG_TB created.   1000000 rows selected from scott@sybo2sz.   1000000 rows inserted into CP_BIG_TB.   1000000 rows committed into CP_BIG_TB at scott@sybo2sz.PL/SQL procedure successfully completed.The elapsed time is 41.84 seconds.The undo size is 0The redo size is 0PL/SQL procedure successfully completed.--上面得到的結果表明，copy命令被使用時不產生undo 和redo--一百萬行資料複製的時間是41.84 seconds--下面清除剛剛複製的目的表scott@SYBO2SZ> drop table CP_BIG_TB purge;--清空緩衝scott@SYBO2SZ> alter system flush buffer_cache;scott@SYBO2SZ> alter system flush shared_pool;--設定新的arraysize與copycommitscott@SYBO2SZ> set arraysize 2000scott@SYBO2SZ> set copycommit 5000--再次呼叫指令碼scott@SYBO2SZ> @/users/robin/dba_scripts/custom/temp/cp_cmd.sqlPL/SQL procedure successfully completed.Array fetch/bind size is 2000. (arraysize is 2000)Will commit after every 5000 array binds. (copycommit is 5000)Maximum long size is 5000. (long is 5000)Table CP_BIG_TB created.   1000000 rows selected from scott@sybo2sz.   1000000 rows inserted into CP_BIG_TB.   1000000 rows committed into CP_BIG_TB at scott@sybo2sz.PL/SQL procedure successfully completed.The elapsed time is 24.65 seconds.The undo size is 0The redo size is 0PL/SQL procedure successfully completed.--從上面的結果可知，後者耗用的時間明顯低於前者，節約了近一半的時間

7、測試用到的指令碼

robin@SZDB:~/dba_scripts/custom/temp> more cp_cmd.sqlSET SERVEROUTPUT ON;VARIABLE start_time NUMBER;VARIABLE end_time NUMBER;VARIABLE v_s_undo NUMBER;VARIABLE v_s_redo NUMBER;VARIABLE v_e_undo NUMBER;VARIABLE v_e_redo NUMBER;VARIABLE v_diff_dt NUMBER;VARIABLE v_diff_undo NUMBER;VARIABLE v_diff_redo NUMBER;--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612BEGIN   SELECT DBMS_UTILITY.get_time INTO :start_time FROM DUAL;   SELECT b.VALUE     INTO :v_s_undo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';   SELECT b.VALUE     INTO :v_s_redo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';END;/COPY from scott/tiger@sybo2sz -to scott/tiger@sybo2sz -create cp_big_tb -using -select * from big_table;BEGIN   SELECT DBMS_UTILITY.get_time INTO :end_time FROM DUAL;   SELECT b.VALUE     INTO :v_e_undo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';   SELECT b.VALUE     INTO :v_e_redo     FROM v$statname a, v$mystat b    WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';END;/BEGIN   :v_diff_dt := round((:end_time - :start_time)/100,2);   :v_diff_undo := :v_e_undo - :v_s_undo;   :v_diff_redo := :v_e_redo - :v_s_redo;   DBMS_OUTPUT.put_line ('The elapsed time is ' || TO_CHAR (:v_diff_dt)||' seconds.');   DBMS_OUTPUT.put_line ('The undo size is ' || TO_CHAR (:v_diff_undo));   DBMS_OUTPUT.put_line ('The redo size is ' || TO_CHAR (:v_diff_redo));END;/

更多參考

DML Error Logging 特性 

PL/SQL --> 遊標

PL/SQL --> 隱式遊標(SQL%FOUND)

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫