亚洲av无码之国产精品网址蜜芽,精品一区二区三区免费毛片爱,亚洲av综合色区无码专区桃色

??Oracle Datapump API 是基于PL/SQL實現的，是命令行方式下的補充。使用Datapump API可以將其邏輯備份特性將其集成到應用程序當中，
基于界面來實現有利于簡化其管理。本文主要描述的使用Datapump API描述各種不同情形的數據導出。

一、演示使用datapump api實現數據導出

--1、導出schema(schema模式)



	DECLARE

	   l_dp_handle        NUMBER;

	   l_last_job_state   VARCHAR2 (30) := 'UNDEFINED';

	   l_job_state        VARCHAR2 (30) := 'UNDEFINED';

	   l_sts              KU$STATUS;

	BEGIN

	   --sepcified operation,job mode

	   l_dp_handle :=

	      DBMS_DATAPUMP.open (operation     => 'EXPORT'

	                        , job_mode      => 'SCHEMA'     

	                        , remote_link   => NULL

	                        , job_name      => 'JOB_EXP1'

	                        , version       => 'LATEST');

	   --specified dumpfile and dump directory

	   DBMS_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 directory

	   DBMS_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 schema

	   DBMS_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、導出特定表table(表模式)



	DECLARE

	   l_dp_handle        NUMBER;

	   l_last_job_state   VARCHAR2 (30) := 'UNDEFINED';

	   l_job_state        VARCHAR2 (30) := 'UNDEFINED';

	   l_sts              KU$STATUS;

	BEGIN

	   l_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);

	   -->如果非當前帳戶，使用下面的過濾條件，即特定schema下的特定表，如為當前帳戶，此過濾條件可省略

	   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、導出schema并過濾掉特定表(使用非當前帳戶導出時應過濾schema)



	DECLARE

	   l_dp_handle   NUMBER;

	BEGIN

	   l_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、導出當前schema下的所有表并過濾特定表



	DECLARE

	   l_dp_handle   NUMBER;

	BEGIN

	   l_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、批量過濾當前用戶下的特定表



	DECLARE

	   l_dp_handle   NUMBER;

	BEGIN

	   l_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、過濾特定表上的特定行

--現在表tb_emp上HIREDATE為日期型，需要按日期進行過濾

	scott@CNMMBO> desc tb_emp

	 Name                                      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.Ch

	      7369 SMITH      19801217

	      7499 ALLEN      19810220

	      7521 WARD       19810222

	      7566 JONES      19810402

	      7654 MARTIN     19810928

	      7698 BLAKE      19810501

	      7782 CLARK      19810609

	      7788 SCOTT      19870419

	      7839 KING       19811117

	      7844 TURNER     19810908

	      7876 ADAMS      19870523

	      7900 JAMES      19811203

	      7902 FORD       19811203

	      7934 MILLER     19820123

	

	15 rows selected.

	scott@CNMMBO> select count(*) from tb_emp where hiredate>='19810311';

	

	  COUNT(*)

	----------

	        11

	    

	DECLARE

	   l_dp_handle   NUMBER;

	BEGIN

	   l_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.log

	Starting "SCOTT"."SYS_EXPORT_TABLE_01":  

	Estimate in progress using BLOCKS method...

	Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

	Total estimation using BLOCKS method: 64 KB

	Processing object type TABLE_EXPORT/TABLE/TABLE

	Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

	. . exported "SCOTT"."TB_EMP"                            7.695 KB      11 rows

	Master 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.dmp

	Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:33:23 */



--7、批量過濾特定表上的特定行

--將下面的代碼包含在PL/SQL塊中，使用游標循環來傳遞需要過濾的表的名字從而生成多個過濾條件

--下面的PL/SQL塊中所有包含ARC字符的表上的特定日期v_split_date的記錄才能被導出

	FOR tab_cur IN (SELECT table_name, num_rows

	                  FROM dba_tables

	                 WHERE table_name LIKE '%ARC%' AND owner='GOEX_ADMIN')

	LOOP

	   dbms_datapump.

	    data_filter (

	      handle       => hand,

	      name         => 'SUBQUERY',

	      VALUE        => 'WHERE BUSINESS_DATE >= ''' || v_split_date || '''',

	      table_name   => '' || tab_cur.table_name || '');

	END LOOP;



--8、錯誤處理

--如果定義了job_name則經常會碰到下列錯誤，如果未指定job_name則有系統自動生成job_name，并由系統自動管理job_name



	DECLARE

	*

	ERROR at line 1:

	ORA-31634: job already exists

	ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

	ORA-06512: at "SYS.DBMS_DATAPUMP", line 911

	ORA-06512: at "SYS.DBMS_DATAPUMP", line 4354

	ORA-06512: at line 7

	

	scott@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_EXP

	

	scott@CNMMBO> drop table job_exp;

	drop table job_exp

	           *

	ERROR at line 1:

	ORA-00054: resource busy and acquire with NOWAIT specified

	

	scott@CNMMBO> SELECT DISTINCT    object_name

	  2                  || '   '

	  3                  || locked_mode

	  4                  || '   '

	  5                  || ctime

	  6                  || '   '

	  7                  || c.SID

	  8                  || '  '

	  9                  || serial#

	 10             FROM v$locked_object a, dba_objects b, v$lock c, v$session d

	 11            WHERE a.object_id = b.object_id

	 12              AND c.SID = a.session_id

	 13              AND c.SID = d.SID;

	

	OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL# 

	-----------------------------------------------------------------------

	JOB_EXP   3   552   1075  799

	

	scott@CNMMBO> alter system kill session '1075,799';

	

	System altered.

	

	scott@CNMMBO> drop table job_exp purge;   -->刪除表之后再次進行導出

	

	Table dropped.  



9、使用視圖監控datapump狀態

	scott@CNMMBO> col owner_name format a15

	scott@CNMMBO> col operation format a15

	scott@CNMMBO> col state format a20

	scott@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                     1



10、使用下面的過程設定并行度

	DBMS_DATAPUMP.set_parallel (hand, 1);

		

11、上述操作所在的演示環境

	scott@CNMMBO> select * from v$version where rownum<2;

	

	BANNER

	----------------------------------------------------------------

	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

使用 Oracle Datapump API 實現數據導出

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频