為什么80%的碼農(nóng)都做不了架構(gòu)師？>>> ??

??Oracle Datapump API 是基于PL/SQL實(shí)現(xiàn)的，是命令行方式下的補(bǔ)充。使用Datapump API可以將其邏輯備份特性將其集成到應(yīng)用程序當(dāng)中，
基于界面來(lái)實(shí)現(xiàn)有利于簡(jiǎn)化其管理。本文主要描述的使用Datapump API描述各種不同情形的數(shù)據(jù)導(dǎo)出。

一、演示使用datapump api實(shí)現(xiàn)數(shù)據(jù)導(dǎo)出

--1、導(dǎo)出schema(schema模式)DECLAREl_dp_handle NUMBER;l_last_job_state VARCHAR2 (30) := 'UNDEFINED';l_job_state VARCHAR2 (30) := 'UNDEFINED';l_sts KU$STATUS;BEGIN--sepcified operation,job model_dp_handle :=DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA' , remote_link => NULL, job_name => 'JOB_EXP1', version => 'LATEST');--specified dumpfile and dump directoryDBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_schema.dmp', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);--specified log file and dump directoryDBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_schema.log', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);--specified fliter for schemaDBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'SCHEMA_EXPR', VALUE => 'IN (''SCOTT'')');DBMS_DATAPUMP.start_job (l_dp_handle);DBMS_DATAPUMP.detach (l_dp_handle);END;/--2、導(dǎo)出特定表table(表模式)DECLAREl_dp_handle NUMBER;l_last_job_state VARCHAR2 (30) := 'UNDEFINED';l_job_state VARCHAR2 (30) := 'UNDEFINED';l_sts KU$STATUS;BEGINl_dp_handle :=DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'JOB_EXP2', version => 'LATEST');DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'emp_tbl.dmp', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'emp_tbl.log', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);-->如果非當(dāng)前帳戶，使用下面的過(guò)濾條件，即特定schema下的特定表，如為當(dāng)前帳戶，此過(guò)濾條件可省略DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'SCHEMA_EXPR', VALUE => 'IN(''SCOTT'')');DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'NAME_EXPR', VALUE => 'IN(''EMP'')');DBMS_DATAPUMP.start_job (l_dp_handle);DBMS_DATAPUMP.detach (l_dp_handle);END;/--3、導(dǎo)出schema并過(guò)濾掉特定表(使用非當(dāng)前帳戶導(dǎo)出時(shí)應(yīng)過(guò)濾schema)DECLAREl_dp_handle NUMBER;BEGINl_dp_handle :=DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA');DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_filter.dmp', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_filter.log', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'SCHEMA_LIST', VALUE => ' ''SCOTT'' ');DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'NAME_EXPR', VALUE => ' !=''EMP'' ', object_type => 'TABLE');DBMS_DATAPUMP.start_job (l_dp_handle);END;/--4、導(dǎo)出當(dāng)前schema下的所有表并過(guò)濾特定表DECLAREl_dp_handle NUMBER;BEGINl_dp_handle :=DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_filter_2.dmp', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_filter_2.log', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'NAME_EXPR', VALUE => ' !=''EMP'' ');DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'NAME_EXPR', VALUE => ' !=''DEPT'' ');DBMS_DATAPUMP.start_job (l_dp_handle);DBMS_DATAPUMP.detach (l_dp_handle);END;/--5、批量過(guò)濾當(dāng)前用戶下的特定表DECLAREl_dp_handle NUMBER;BEGINl_dp_handle :=DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_filter_3.dmp', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);DBMS_DATAPUMP.add_file (handle => l_dp_handle, filename => 'scott_filter_3.log', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'NAME_EXPR', VALUE => ' NOT LIKE ''T%'' ');DBMS_DATAPUMP.start_job (l_dp_handle);DBMS_DATAPUMP.detach (l_dp_handle);END;//**************************************************//* Author: Robinson Cheng *//* Blog: http://blog.csdn.net/robinson_0612 *//* MSN: robinson_0612@hotmail.com *//* QQ: 645746311 *//**************************************************/ --6、過(guò)濾特定表上的特定行 --現(xiàn)在表tb_emp上HIREDATE為日期型，需要按日期進(jìn)行過(guò)濾scott@CNMMBO> desc tb_empName Null? Type----------------------------------------- -------- ----------------------------EMPNO NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE VARCHAR2(10)SAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)scott@CNMMBO> select empno,ename,hiredate from tb_emp;EMPNO ENAME HIREDATE---------- ---------- ----------9999 Ro.Ch7369 SMITH 198012177499 ALLEN 198102207521 WARD 198102227566 JONES 198104027654 MARTIN 198109287698 BLAKE 198105017782 CLARK 198106097788 SCOTT 198704197839 KING 198111177844 TURNER 198109087876 ADAMS 198705237900 JAMES 198112037902 FORD 198112037934 MILLER 1982012315 rows selected.scott@CNMMBO> select count(*) from tb_emp where hiredate>='19810311';COUNT(*)----------11DECLAREl_dp_handle NUMBER;BEGINl_dp_handle :=DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');dbms_datapump.add_file (handle => l_dp_handle, filename => 'scott_tb_emp.dmp', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$file_type_dump_file);dbms_datapump.add_file (handle => l_dp_handle, filename => 'scott_tb_emp.log', directory => 'DB_DUMP_DIR', filetype => DBMS_DATAPUMP.KU$file_type_log_file);DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => 'NAME_EXPR', VALUE => ' =''TB_EMP'' ', object_type => 'TABLE');DBMS_DATAPUMP.data_filter( handle => l_dp_handle, name => 'SUBQUERY', VALUE => 'WHERE HIREDATE >=''19810311''', table_name => 'TB_EMP' ); DBMS_DATAPUMP.start_job (l_dp_handle);DBMS_DATAPUMP.detach (l_dp_handle);END;//*oracle@SZDB:/u02/database/CNMMBO/BNR/dump> more scott_tb_emp.logStarting "SCOTT"."SYS_EXPORT_TABLE_01": Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."TB_EMP" 7.695 KB 11 rowsMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:/u02/database/CNMMBO/BNR/dump/scott_tb_emp.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:33:23 */--7、批量過(guò)濾特定表上的特定行 --將下面的代碼包含在PL/SQL塊中，使用游標(biāo)循環(huán)來(lái)傳遞需要過(guò)濾的表的名字從而生成多個(gè)過(guò)濾條件 --下面的PL/SQL塊中所有包含ARC字符的表上的特定日期v_split_date的記錄才能被導(dǎo)出FOR tab_cur IN (SELECT table_name, num_rowsFROM dba_tablesWHERE table_name LIKE '%ARC%' AND owner='GOEX_ADMIN')LOOPdbms_datapump.data_filter (handle => hand,name => 'SUBQUERY',VALUE => 'WHERE BUSINESS_DATE >= ''' || v_split_date || '''',table_name => '' || tab_cur.table_name || '');END LOOP;--8、錯(cuò)誤處理 --如果定義了job_name則經(jīng)常會(huì)碰到下列錯(cuò)誤，如果未指定job_name則有系統(tǒng)自動(dòng)生成job_name，并由系統(tǒng)自動(dòng)管理job_nameDECLARE*ERROR at line 1:ORA-31634: job already existsORA-06512: at "SYS.DBMS_SYS_ERROR", line 79ORA-06512: at "SYS.DBMS_DATAPUMP", line 911ORA-06512: at "SYS.DBMS_DATAPUMP", line 4354ORA-06512: at line 7scott@CNMMBO> ho oerr ora 31634/*31634, 00000, "job already exists"// *Cause: Job creation or restart failed because a job having the selected // name is currently executing. This also generally indicates that// a Master Table with that job name exists in the user schema. Refer// to any following error messages for clarification.// *Action: Select a different job name, or stop the currently executing job // and re-try the operation (may require a DROP on the Master Table). */scott@CNMMBO> select table_name from user_tables where table_name like 'JOB%';TABLE_NAME------------------------------JOB_EXPscott@CNMMBO> drop table job_exp;drop table job_exp*ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specifiedscott@CNMMBO> SELECT DISTINCT object_name2 || ' '3 || locked_mode4 || ' '5 || ctime6 || ' '7 || c.SID8 || ' '9 || serial#10 FROM v$locked_object a, dba_objects b, v$lock c, v$session d11 WHERE a.object_id = b.object_id12 AND c.SID = a.session_id13 AND c.SID = d.SID;OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL# -----------------------------------------------------------------------JOB_EXP 3 552 1075 799scott@CNMMBO> alter system kill session '1075,799';System altered.scott@CNMMBO> drop table job_exp purge; -->刪除表之后再次進(jìn)行導(dǎo)出Table dropped. 9、使用視圖監(jiān)控datapump狀態(tài)scott@CNMMBO> col owner_name format a15scott@CNMMBO> col operation format a15scott@CNMMBO> col state format a20scott@CNMMBO> select owner_name,job_name,operation,job_mode,state,degree from dba_datapump_jobs;OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE--------------- --------------- --------------- ---------- -------------------- ----------SCOTT JOB_EXP1 EXPORT SCHEMA EXECUTING 110、使用下面的過(guò)程設(shè)定并行度DBMS_DATAPUMP.set_parallel (hand, 1);11、上述操作所在的演示環(huán)境scott@CNMMBO> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

二、幾點(diǎn)注意事項(xiàng)
1、使用schema模式導(dǎo)出時(shí)，如果導(dǎo)出的schema為當(dāng)前schema,則不需要指定schema過(guò)濾條件，否則需要對(duì)schema進(jìn)行過(guò)濾
2、使用table表模式導(dǎo)出時(shí)，如果導(dǎo)出的表為當(dāng)前schema,則不需要指定schema過(guò)濾條件，否則需要對(duì)schema進(jìn)行過(guò)濾
3、對(duì)于過(guò)濾表上的特定記錄可以使用多種SQL表達(dá)式，如 LIKE, NOT LIKE,IN, NOT IN, = , != 符號(hào)等
4、需要注意單引號(hào)的使用，尤其是在字符型的數(shù)據(jù)類型時(shí)，兩個(gè)單引號(hào)代表一個(gè)引號(hào)
5、如果在導(dǎo)出時(shí)存在同樣的dump文件和日志文件時(shí)PL/SQL塊將執(zhí)行失敗，刪除或通過(guò)寫PL/SQL來(lái)判斷文件是否存在，如存在是否覆蓋等
6、如果指定了job_name，則當(dāng)前drop失敗之后，再次執(zhí)行時(shí)會(huì)碰到j(luò)ob已經(jīng)存在的提示，建議讓系統(tǒng)自動(dòng)生成job_name簡(jiǎn)化管理

三、更多參考

數(shù)據(jù)泵 EXPDP 導(dǎo)出工具的使用

數(shù)據(jù)泵 IMPDP 導(dǎo)入工具的使用

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

?

原文鏈接： http://blog.csdn.net/robinson_0612/article/details/7195849

轉(zhuǎn)載于:https://my.oschina.net/dtec/blog/47319

總結(jié)

以上是生活随笔為你收集整理的使用 Oracle Datapump API 实现数据导出的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。