使用 Oracle Datapump API 实现数据导出

  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简化管理

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

【动手实践】Oracle 12.2新特性:多列列表分区和外部表分区

在Oracle 12.2版本中,增加了大量的分区新特性,这其中包括: 自动的列表分区创建 在线的普通表转换分区表 支持只读分区和读写分区混合 以下介绍的三个特...

34450
来自专栏拂晓风起

[C#]log4net写SQLServer数据库日志的配置方法

16840
来自专栏Netkiller

MySQL 大数据操作注意事项

MySQL 大数据操作注意事项 http://netkiller.github.io/journal/mysql.parallel.html 摘要 ---- 目...

40580
来自专栏xingoo, 一个梦想做发明家的程序员

oracle数据结构

 数据类型: 1 字符数据:CHAR VARCHAR NCHAR NVARCHAR2 LONG CLOB NCLOB  2 数字数据类型:NUMBER 唯一用来...

22260
来自专栏乐沙弥的世界

SQL基础-->数据库事务(TRANSACTION)

数据库事务是指作为单个逻辑工作单元执行的一系列操作,可以认为事务就是一组不可分割的SQL语句

8630
来自专栏杨建荣的学习笔记

浅谈exp/imp(上) (r5笔记第81天)

作为DBA,经常需要在不同数据库环境间做数据的导入导出,exp/imp就是这样的轻便快捷的客户端工具,可以很方便的在不同数据库之间转移数据对象,即使数据库位于不...

30980
来自专栏杨建荣的学习笔记

关于trigger过滤最大值的问题(54天)

今天碰到一个问题,开发有一个比较紧的需要,想问问我数据库这边能不能帮上忙。 如果开发那边来做,需要改代码,如果数据库这边能临时支持,代码就可以多做些测试,然后再...

30050
来自专栏Android相关

SQLite---使用触发器(Trigger)

当数据库中的数据太多时,往往需要进行清理,将一些过时的数据删除,但是往往找不到合适的时机进行清理。于是SQLite提供了Trigger,当某些事件发生时,可以触...

19350
来自专栏数据库新发现

Oracle9i新特性-索引监视及注意事项[修正版]

Last Updated: Saturday, 2004-12-04 10:28 Eygle

10030
来自专栏james大数据架构

SQL SERVER 内存分配及常见内存问题 DMV查询

内存动态管理视图(DMV):   从sys.dm_os_memory_clerks开始。 SELECT  [type] ,   SUM(virtual_mem...

220100

扫码关注云+社区

领取腾讯云代金券