加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

使用 Oracle Datapump 实现数据导出

(2014-03-04 11:13:23)
标签：

工具

不需要

条件

建议

志文

it

 分类： 数据库
Oracle Datapump API 是基于PL/SQL实现的，是命令行方式下的补充。使用Datapump API可以将其逻辑备份特性将其集成到应用程序当中，
基于界面来实现有利于简化其管理。本文主要描述的使用Datapump API描述各种不同情形的数据导出。
一、演示使用datapump api实现数据导出
[sql] view plaincopyprint?
--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; 
    / 
     
     
     
     
     
     
     
[sql] view plaincopyprint?
--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; 
    / 
     
 
--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 
     
     
    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，则当前drop失败之后，再次执行时会碰到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

 

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有