使用 Oracle Datapump API 實現資料匯出

  Oracle Datapump API 是基於PL/SQL實現的，是命令列方式下的補充。使用Datapump API可以將其邏輯備份特性將其整合到應用程式當中，
基於介面來實現有利於簡化其管理。本文主要描述的使用Datapump API描述各種不同情形的資料匯出。

一、示範使用datapump api實現資料匯出

--1、匯出schema(schema模式)</p><p>DECLARE<br /> l_dp_handle NUMBER;<br /> l_last_job_state VARCHAR2 (30) := 'UNDEFINED';<br /> l_job_state VARCHAR2 (30) := 'UNDEFINED';<br /> l_sts KU$STATUS;<br />BEGIN<br /> --sepcified operation,job mode<br /> l_dp_handle :=<br /> DBMS_DATAPUMP.open (operation => 'EXPORT'<br /> , job_mode => 'SCHEMA'<br /> , remote_link => NULL<br /> , job_name => 'JOB_EXP1'<br /> , version => 'LATEST');<br /> --specified dumpfile and dump directory<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_schema.dmp'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);<br /> --specified log file and dump directory<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_schema.log'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);<br /> --specified fliter for schema<br /> DBMS_DATAPUMP.<br /> metadata_filter (handle => l_dp_handle<br /> , name => 'SCHEMA_EXPR'<br /> , VALUE => 'IN (''SCOTT'')');<br /> DBMS_DATAPUMP.start_job (l_dp_handle);<br /> DBMS_DATAPUMP.detach (l_dp_handle);<br />END;<br />/</p><p>--2、匯出特定表table(表模式)</p><p>DECLARE<br /> l_dp_handle NUMBER;<br /> l_last_job_state VARCHAR2 (30) := 'UNDEFINED';<br /> l_job_state VARCHAR2 (30) := 'UNDEFINED';<br /> l_sts KU$STATUS;<br />BEGIN<br /> l_dp_handle :=<br /> DBMS_DATAPUMP.open (operation => 'EXPORT'<br /> , job_mode => 'TABLE'<br /> , remote_link => NULL<br /> , job_name => 'JOB_EXP2'<br /> , version => 'LATEST');<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'emp_tbl.dmp'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'emp_tbl.log'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);<br /> -->如果非當前帳戶，使用下面的過濾條件，即特定schema下的特定表，如為當前帳戶，此過濾條件可省略<br /> DBMS_DATAPUMP.<br /> metadata_filter (handle => l_dp_handle<br /> , name => 'SCHEMA_EXPR'<br /> , VALUE => 'IN(''SCOTT'')');<br /> DBMS_DATAPUMP.<br /> metadata_filter (handle => l_dp_handle<br /> , name => 'NAME_EXPR'<br /> , VALUE => 'IN(''EMP'')');<br /> DBMS_DATAPUMP.start_job (l_dp_handle);<br /> DBMS_DATAPUMP.detach (l_dp_handle);<br />END;<br />/</p><p>--3、匯出schema並過濾掉特定表(使用非當前帳戶匯出時應過濾schema)</p><p>DECLARE<br /> l_dp_handle NUMBER;<br />BEGIN<br /> l_dp_handle :=<br /> DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA');<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_filter.dmp'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_filter.log'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);<br /> DBMS_DATAPUMP.<br /> metadata_filter (handle => l_dp_handle<br /> , name => 'SCHEMA_LIST'<br /> , VALUE => ' ''SCOTT'' ');<br /> DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle<br /> , name => 'NAME_EXPR'<br /> , VALUE => ' !=''EMP'' '<br /> , object_type => 'TABLE');<br /> DBMS_DATAPUMP.start_job (l_dp_handle);<br />END;<br />/</p><p>--4、匯出當前schema下的所有表並過濾特定表</p><p>DECLARE<br /> l_dp_handle NUMBER;<br />BEGIN<br /> l_dp_handle :=<br /> DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_filter_2.dmp'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_filter_2.log'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);<br /> DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle<br /> , name => 'NAME_EXPR'<br /> , VALUE => ' !=''EMP'' ');<br /> DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle<br /> , name => 'NAME_EXPR'<br /> , VALUE => ' !=''DEPT'' ');<br /> DBMS_DATAPUMP.start_job (l_dp_handle);<br /> DBMS_DATAPUMP.detach (l_dp_handle);<br />END;<br />/</p><p>--5、批量過濾目前使用者下的特定表</p><p>DECLARE<br /> l_dp_handle NUMBER;<br />BEGIN<br /> l_dp_handle :=<br /> DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_filter_3.dmp'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);<br /> DBMS_DATAPUMP.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_filter_3.log'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);<br /> DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle<br /> , name => 'NAME_EXPR'<br /> , VALUE => ' NOT LIKE ''T%'' ');<br /> DBMS_DATAPUMP.start_job (l_dp_handle);<br /> DBMS_DATAPUMP.detach (l_dp_handle);<br />END;<br />/</p><p>/**************************************************/<br />/* Author: Robinson Cheng */<br />/* Blog: http://blog.csdn.net/robinson_0612 */<br />/* MSN: robinson_0612@hotmail.com */<br />/* QQ: 645746311 */<br />/**************************************************/<br />--6、過濾特定表上的特定行<br />--現在表tb_emp上HIREDATE為日期型，需要按日期進行過濾<br />scott@CNMMBO> desc tb_emp<br /> Name Null? Type<br /> ----------------------------------------- -------- ----------------------------<br /> EMPNO NUMBER(4)<br /> ENAME VARCHAR2(10)<br /> JOB VARCHAR2(9)<br /> MGR NUMBER(4)<br /> HIREDATE VARCHAR2(10)<br /> SAL NUMBER(7,2)<br /> COMM NUMBER(7,2)<br /> DEPTNO NUMBER(2)</p><p>scott@CNMMBO> select empno,ename,hiredate from tb_emp;</p><p> EMPNO ENAME HIREDATE<br />---------- ---------- ----------<br /> 9999 Ro.Ch<br /> 7369 SMITH 19801217<br /> 7499 ALLEN 19810220<br /> 7521 WARD 19810222<br /> 7566 JONES 19810402<br /> 7654 MARTIN 19810928<br /> 7698 BLAKE 19810501<br /> 7782 CLARK 19810609<br /> 7788 SCOTT 19870419<br /> 7839 KING 19811117<br /> 7844 TURNER 19810908<br /> 7876 ADAMS 19870523<br /> 7900 JAMES 19811203<br /> 7902 FORD 19811203<br /> 7934 MILLER 19820123</p><p>15 rows selected.<br />scott@CNMMBO> select count(*) from tb_emp where hiredate>='19810311';</p><p> COUNT(*)<br />----------<br /> 11</p><p>DECLARE<br /> l_dp_handle NUMBER;<br />BEGIN<br /> l_dp_handle :=<br /> DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');<br /> dbms_datapump.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_tb_emp.dmp'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$file_type_dump_file);<br /> dbms_datapump.<br /> add_file (handle => l_dp_handle<br /> , filename => 'scott_tb_emp.log'<br /> , directory => 'DB_DUMP_DIR'<br /> , filetype => DBMS_DATAPUMP.KU$file_type_log_file);<br /> DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle<br /> , name => 'NAME_EXPR'<br /> , VALUE => ' =''TB_EMP'' '<br /> , object_type => 'TABLE');<br /> DBMS_DATAPUMP.data_filter( handle => l_dp_handle<br /> , name => 'SUBQUERY'<br /> , VALUE => 'WHERE HIREDATE >=''19810311'''<br /> , table_name => 'TB_EMP' );<br /> DBMS_DATAPUMP.start_job (l_dp_handle);<br /> DBMS_DATAPUMP.detach (l_dp_handle);<br />END;<br />/<br />/*<br />oracle@SZDB:/u02/database/CNMMBO/BNR/dump> more scott_tb_emp.log<br />Starting "SCOTT"."SYS_EXPORT_TABLE_01":<br />Estimate in progress using BLOCKS method...<br />Processing object type TABLE_EXPORT/TABLE/TABLE_DATA<br />Total estimation using BLOCKS method: 64 KB<br />Processing object type TABLE_EXPORT/TABLE/TABLE<br />Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS<br />. . exported "SCOTT"."TB_EMP" 7.695 KB 11 rows<br />Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded<br />******************************************************************************<br />Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:<br /> /u02/database/CNMMBO/BNR/dump/scott_tb_emp.dmp<br />Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:33:23 */</p><p>--7、批量過濾特定表上的特定行<br />--將下面的程式碼封裝含在PL/SQL塊中，使用遊標迴圈來傳遞需要過濾的表的名字從而產生多個過濾條件<br />--下面的PL/SQL塊中所有包含ARC字元的表上的特定日期v_split_date的記錄才能被匯出<br />FOR tab_cur IN (SELECT table_name, num_rows<br /> FROM dba_tables<br /> WHERE table_name LIKE '%ARC%' AND owner='GOEX_ADMIN')<br />LOOP<br /> dbms_datapump.<br /> data_filter (<br /> handle => hand,<br /> name => 'SUBQUERY',<br /> VALUE => 'WHERE BUSINESS_DATE >= ''' || v_split_date || '''',<br /> table_name => '' || tab_cur.table_name || '');<br />END LOOP;</p><p>--8、錯誤處理<br />--如果定義了job_name則經常會碰到下列錯誤，如果未指定job_name則有系統自動產生job_name，並由系統自動管理job_name</p><p>DECLARE<br />*<br />ERROR at line 1:<br />ORA-31634: job already exists<br />ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79<br />ORA-06512: at "SYS.DBMS_DATAPUMP", line 911<br />ORA-06512: at "SYS.DBMS_DATAPUMP", line 4354<br />ORA-06512: at line 7</p><p>scott@CNMMBO> ho oerr ora 31634<br />/*<br />31634, 00000, "job already exists"<br />// *Cause: Job creation or restart failed because a job having the selected<br />// name is currently executing. This also generally indicates that<br />// a Master Table with that job name exists in the user schema. Refer<br />// to any following error messages for clarification.<br />// *Action: Select a different job name, or stop the currently executing job<br />// and re-try the operation (may require a DROP on the Master Table). */</p><p>scott@CNMMBO> select table_name from user_tables where table_name like 'JOB%';</p><p>TABLE_NAME<br />------------------------------<br />JOB_EXP</p><p>scott@CNMMBO> drop table job_exp;<br />drop table job_exp<br /> *<br />ERROR at line 1:<br />ORA-00054: resource busy and acquire with NOWAIT specified</p><p>scott@CNMMBO> SELECT DISTINCT object_name<br /> 2 || ' '<br /> 3 || locked_mode<br /> 4 || ' '<br /> 5 || ctime<br /> 6 || ' '<br /> 7 || c.SID<br /> 8 || ' '<br /> 9 || serial#<br /> 10 FROM v$locked_object a, dba_objects b, v$lock c, v$session d<br /> 11 WHERE a.object_id = b.object_id<br /> 12 AND c.SID = a.session_id<br /> 13 AND c.SID = d.SID;</p><p>OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL#<br />-----------------------------------------------------------------------<br />JOB_EXP 3 552 1075 799</p><p>scott@CNMMBO> alter system kill session '1075,799';</p><p>System altered.</p><p>scott@CNMMBO> drop table job_exp purge; -->刪除表之後再次進行匯出</p><p>Table dropped. </p><p>9、使用視圖監控datapump狀態<br />scott@CNMMBO> col owner_name format a15<br />scott@CNMMBO> col operation format a15<br />scott@CNMMBO> col state format a20<br />scott@CNMMBO> select owner_name,job_name,operation,job_mode,state,degree from dba_datapump_jobs;</p><p>OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE<br />--------------- --------------- --------------- ---------- -------------------- ----------<br />SCOTT JOB_EXP1 EXPORT SCHEMA EXECUTING 1</p><p>10、使用下面的過程設定並行度<br />DBMS_DATAPUMP.set_parallel (hand, 1);</p><p>11、上述操作所在的示範環境<br />scott@CNMMBO> select * from v$version where rownum<2;</p><p>BANNER<br />----------------------------------------------------------------<br />Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

二、幾點注意事項
1、使用schema模式匯出時，如果匯出的schema為當前schema,則不需要指定schema過濾條件，否則需要對schema進行過濾
2、使用table表模式匯出時，如果匯出的表為當前schema,則不需要指定schema過濾條件，否則需要對schema進行過濾
3、對於過濾表上的特定記錄可以使用多種SQL運算式，如 LIKE, NOT LIKE,IN, NOT IN, = , != 符號等
4、需要注意單引號的使用，尤其是在字元型的資料類型時，兩個單引號代表一個引號
5、如果在匯出時存在同樣的dump檔案和記錄檔時PL/SQL塊將執行失敗，刪除或通過寫PL/SQL來判斷檔案是否存在，如存在是否覆蓋等
6、如果指定了job_name，則當前job失敗之後，再次執行時會碰到job已經存在的提示，建議讓系統自動產生job_name簡化管理

三、更多參考

資料泵 EXPDP 匯出工具的使用

資料泵 IMPDP 匯入工具的使用

expdp impdp中 exclude/include 的使用

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_datpmp.htm
https://forums.oracle.com/forums/thread.jspa?threadID=837324
http://psoug.org/reference/dbms_datapump.html