程序博客网 > bodog信誉第一js

使用 Oracle Datapump API 实现数据导出

来源：互联网 发布：bodog信誉第一js 编辑：程序博客网 时间：2024/04/29 17:48

  Oracle Datapump API 是基于PL/SQL实现的，是命令行方式下的补充。使用Datapump API可以将其逻辑备份特性将其集成到应用程序当中，
基于界面来实现有利于简化其管理。本文主要描述的使用Datapump API描述各种不同情形的数据导出。

一、演示使用datapump api实现数据导出

[sql] view plaincopyprint?
  1. --1、导出schema(schema模式)   
  2.   
  3.     DECLARE  
  4.        l_dp_handle        NUMBER;  
  5.        l_last_job_state   VARCHAR2 (30) := 'UNDEFINED';  
  6.        l_job_state        VARCHAR2 (30) := 'UNDEFINED';  
  7.        l_sts              KU$STATUS;  
  8.     BEGIN  
  9.        --sepcified operation,job mode  
  10.        l_dp_handle :=  
  11.           DBMS_DATAPUMP.open (operation     => 'EXPORT'  
  12.                             , job_mode      => 'SCHEMA'       
  13.                             , remote_link   => NULL  
  14.                             , job_name      => 'JOB_EXP1'  
  15.                             , version       => 'LATEST');  
  16.        --specified dumpfile and dump directory  
  17.        DBMS_DATAPUMP.  
  18.         add_file (handle      => l_dp_handle  
  19.                 , filename    => 'scott_schema.dmp'  
  20.                 , directory   => 'DB_DUMP_DIR'  
  21.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);  
  22.        --specified log file and dump directory  
  23.        DBMS_DATAPUMP.  
  24.         add_file (handle      => l_dp_handle  
  25.                 , filename    => 'scott_schema.log'  
  26.                 , directory   => 'DB_DUMP_DIR'  
  27.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);  
  28.        --specified fliter for schema   
  29.        DBMS_DATAPUMP.  
  30.         metadata_filter (handle   => l_dp_handle  
  31.                        , name     => 'SCHEMA_EXPR'  
  32.                        , VALUE    => 'IN (''SCOTT'')');  
  33.        DBMS_DATAPUMP.start_job (l_dp_handle);  
  34.        DBMS_DATAPUMP.detach (l_dp_handle);  
  35.     END;  
  36.     /  
  37.   
  38. --2、导出特定表table(表模式)   
  39.   
  40.     DECLARE  
  41.        l_dp_handle        NUMBER;  
  42.        l_last_job_state   VARCHAR2 (30) := 'UNDEFINED';  
  43.        l_job_state        VARCHAR2 (30) := 'UNDEFINED';  
  44.        l_sts              KU$STATUS;  
  45.     BEGIN  
  46.        l_dp_handle :=  
  47.           DBMS_DATAPUMP.open (operation     => 'EXPORT'  
  48.                             , job_mode      => 'TABLE'  
  49.                             , remote_link   => NULL  
  50.                             , job_name      => 'JOB_EXP2'  
  51.                             , version       => 'LATEST');  
  52.        DBMS_DATAPUMP.  
  53.         add_file (handle      => l_dp_handle  
  54.                 , filename    => 'emp_tbl.dmp'  
  55.                 , directory   => 'DB_DUMP_DIR'  
  56.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);  
  57.        DBMS_DATAPUMP.  
  58.         add_file (handle      => l_dp_handle  
  59.                 , filename    => 'emp_tbl.log'  
  60.                 , directory   => 'DB_DUMP_DIR'  
  61.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);  
  62.        -->如果非当前帐户，使用下面的过滤条件，即特定schema下的特定表，如为当前帐户，此过滤条件可省略  
  63.        DBMS_DATAPUMP.  
  64.         metadata_filter (handle   => l_dp_handle  
  65.                        , name     => 'SCHEMA_EXPR'  
  66.                        , VALUE    => 'IN(''SCOTT'')');  
  67.        DBMS_DATAPUMP.  
  68.         metadata_filter (handle   => l_dp_handle  
  69.                        , name     => 'NAME_EXPR'  
  70.                        , VALUE    => 'IN(''EMP'')');  
  71.        DBMS_DATAPUMP.start_job (l_dp_handle);  
  72.        DBMS_DATAPUMP.detach (l_dp_handle);  
  73.     END;  
  74.     /  
  75.   
  76. --3、导出schema并过滤掉特定表(使用非当前帐户导出时应过滤schema)   
  77.   
  78.     DECLARE  
  79.        l_dp_handle   NUMBER;  
  80.     BEGIN  
  81.        l_dp_handle :=  
  82.           DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA');  
  83.        DBMS_DATAPUMP.  
  84.         add_file (handle      => l_dp_handle  
  85.                 , filename    => 'scott_filter.dmp'  
  86.                 , directory   => 'DB_DUMP_DIR'  
  87.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);  
  88.        DBMS_DATAPUMP.  
  89.         add_file (handle      => l_dp_handle  
  90.                 , filename    => 'scott_filter.log'  
  91.                 , directory   => 'DB_DUMP_DIR'  
  92.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);  
  93.        DBMS_DATAPUMP.  
  94.         metadata_filter (handle   => l_dp_handle  
  95.                        , name     => 'SCHEMA_LIST'  
  96.                        , VALUE    => ' ''SCOTT'' ');  
  97.        DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle  
  98.                                     , name          => 'NAME_EXPR'  
  99.                                     , VALUE         => ' !=''EMP'' '  
  100.                                     , object_type   => 'TABLE');  
  101.        DBMS_DATAPUMP.start_job (l_dp_handle);  
  102.     END;  
  103.     /  
  104.   
  105. --4、导出当前schema下的所有表并过滤特定表   
  106.   
  107.     DECLARE  
  108.        l_dp_handle   NUMBER;  
  109.     BEGIN  
  110.        l_dp_handle :=  
  111.           DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');  
  112.        DBMS_DATAPUMP.  
  113.         add_file (handle      => l_dp_handle  
  114.                 , filename    => 'scott_filter_2.dmp'  
  115.                 , directory   => 'DB_DUMP_DIR'  
  116.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);  
  117.        DBMS_DATAPUMP.  
  118.         add_file (handle      => l_dp_handle  
  119.                 , filename    => 'scott_filter_2.log'  
  120.                 , directory   => 'DB_DUMP_DIR'  
  121.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);  
  122.        DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle  
  123.                                     , name          => 'NAME_EXPR'  
  124.                                     , VALUE         => ' !=''EMP'' ');  
  125.        DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle  
  126.                                     , name          => 'NAME_EXPR'  
  127.                                     , VALUE         => ' !=''DEPT'' ');  
  128.        DBMS_DATAPUMP.start_job (l_dp_handle);  
  129.        DBMS_DATAPUMP.detach (l_dp_handle);  
  130.     END;  
  131.     /  
  132.   
  133. --5、批量过滤当前用户下的特定表   
  134.   
  135.     DECLARE  
  136.        l_dp_handle   NUMBER;  
  137.     BEGIN  
  138.        l_dp_handle :=  
  139.           DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');  
  140.        DBMS_DATAPUMP.  
  141.         add_file (handle      => l_dp_handle  
  142.                 , filename    => 'scott_filter_3.dmp'  
  143.                 , directory   => 'DB_DUMP_DIR'  
  144.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);  
  145.        DBMS_DATAPUMP.  
  146.         add_file (handle      => l_dp_handle  
  147.                 , filename    => 'scott_filter_3.log'  
  148.                 , directory   => 'DB_DUMP_DIR'  
  149.                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);  
  150.        DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle  
  151.                                     , name          => 'NAME_EXPR'  
  152.                                     , VALUE         => ' NOT LIKE ''T%'' ');  
  153.        DBMS_DATAPUMP.start_job (l_dp_handle);  
  154.        DBMS_DATAPUMP.detach (l_dp_handle);  
  155.     END;  
  156.     /  
  157.       
  158.     /**************************************************/  
  159.     /* Author: Robinson Cheng                         */  
  160.     /* Blog:   http://blog.csdn.net/robinson_0612     */  
  161.     /* MSN:    robinson_0612@hotmail.com              */  
  162.     /* QQ:     645746311                              */  
  163.     /**************************************************/  
[sql] view plaincopyprint?
  1. --6、过滤特定表上的特定行   
  2. --现在表tb_emp上HIREDATE为日期型，需要按日期进行过滤   
  3.     scott@CNMMBO> desc tb_emp  
  4.      Name                                      Null?    Type  
  5.      ----------------------------------------- -------- ----------------------------  
  6.      EMPNO                                              NUMBER(4)  
  7.      ENAME                                              VARCHAR2(10)  
  8.      JOB                                                VARCHAR2(9)  
  9.      MGR                                                NUMBER(4)  
  10.      HIREDATE                                           VARCHAR2(10)  
  11.      SAL                                                NUMBER(7,2)  
  12.      COMM                                               NUMBER(7,2)  
  13.      DEPTNO                                             NUMBER(2)  
  14.       
  15.     scott@CNMMBO> select empno,ename,hiredate from tb_emp;  
  16.       
  17.          EMPNO ENAME      HIREDATE  
  18.     ---------- ---------- ----------   
  19.           9999 Ro.Ch  
  20.           7369 SMITH      19801217  
  21.           7499 ALLEN      19810220  
  22.           7521 WARD       19810222  
  23.           7566 JONES      19810402  
  24.           7654 MARTIN     19810928  
  25.           7698 BLAKE      19810501  
  26.           7782 CLARK      19810609  
  27.           7788 SCOTT      19870419  
  28.           7839 KING       19811117  
  29.           7844 TURNER     19810908  
  30.           7876 ADAMS      19870523  
  31.           7900 JAMES      19811203  
  32.           7902 FORD       19811203  
  33.           7934 MILLER     19820123  
  34.       
  35.     15 rows selected.  
  36.     scott@CNMMBO> select count(*) from tb_emp where hiredate>='19810311';  
  37.       
  38.       COUNT(*)  
  39.     ----------   
  40.             11  
  41.           
  42.     DECLARE  
  43.        l_dp_handle   NUMBER;  
  44.     BEGIN  
  45.        l_dp_handle :=  
  46.           DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');  
  47.        dbms_datapump.  
  48.         add_file (handle      => l_dp_handle  
  49.                 , filename    => 'scott_tb_emp.dmp'  
  50.                 , directory   => 'DB_DUMP_DIR'  
  51.                 , filetype    => DBMS_DATAPUMP.KU$file_type_dump_file);  
  52.        dbms_datapump.  
  53.         add_file (handle      => l_dp_handle  
  54.                 , filename    => 'scott_tb_emp.log'  
  55.                 , directory   => 'DB_DUMP_DIR'  
  56.                 , filetype    => DBMS_DATAPUMP.KU$file_type_log_file);  
  57.        DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle  
  58.                                     , name          => 'NAME_EXPR'  
  59.                                     , VALUE         => ' =''TB_EMP'' '  
  60.                                     , object_type   => 'TABLE');  
  61.      DBMS_DATAPUMP.data_filter( handle       => l_dp_handle  
  62.                                 , name         => 'SUBQUERY'  
  63.                                 , VALUE        => 'WHERE HIREDATE >=''19810311'''  
  64.                                 , table_name   => 'TB_EMP' );                                  
  65.        DBMS_DATAPUMP.start_job (l_dp_handle);  
  66.        DBMS_DATAPUMP.detach (l_dp_handle);  
  67.     END;  
  68.     /  
  69.     /*  
  70.     oracle@SZDB:/u02/database/CNMMBO/BNR/dump> more scott_tb_emp.log  
  71.     Starting "SCOTT"."SYS_EXPORT_TABLE_01":    
  72.     Estimate in progress using BLOCKS method...  
  73.     Processing object type TABLE_EXPORT/TABLE/TABLE_DATA  
  74.     Total estimation using BLOCKS method: 64 KB  
  75.     Processing object type TABLE_EXPORT/TABLE/TABLE  
  76.     Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS  
  77.     . . exported "SCOTT"."TB_EMP"                            7.695 KB      11 rows  
  78.     Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded  
  79.     ******************************************************************************  
  80.     Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:  
  81.       /u02/database/CNMMBO/BNR/dump/scott_tb_emp.dmp  
  82.     Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:33:23 */  
  83.   
  84. --7、批量过滤特定表上的特定行   
  85. --将下面的代码包含在PL/SQL块中，使用游标循环来传递需要过滤的表的名字从而生成多个过滤条件  
  86. --下面的PL/SQL块中所有包含ARC字符的表上的特定日期v_split_date的记录才能被导出  
  87.     FOR tab_cur IN (SELECT table_name, num_rows  
  88.                       FROM dba_tables  
  89.                      WHERE table_name LIKE '%ARC%' AND owner='GOEX_ADMIN')  
  90.     LOOP  
  91.        dbms_datapump.  
  92.         data_filter (  
  93.           handle       => hand,  
  94.           name         => 'SUBQUERY',  
  95.           VALUE        => 'WHERE BUSINESS_DATE >= ''' || v_split_date || '''',  
  96.           table_name   => '' || tab_cur.table_name || '');  
  97.     END LOOP;  
  98.   
  99. --8、错误处理   
  100. --如果定义了job_name则经常会碰到下列错误，如果未指定job_name则有系统自动生成job_name，并由系统自动管理job_name  
  101.   
  102.     DECLARE  
  103.     *  
  104.     ERROR at line 1:  
  105.     ORA-31634: job already exists  
  106.     ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79  
  107.     ORA-06512: at "SYS.DBMS_DATAPUMP", line 911  
  108.     ORA-06512: at "SYS.DBMS_DATAPUMP", line 4354  
  109.     ORA-06512: at line 7  
  110.       
  111.     scott@CNMMBO> ho oerr ora 31634  
  112.     /*  
  113.     31634, 00000, "job already exists"  
  114.     // *Cause:  Job creation or restart failed because a job having the selected    
  115.     //          name is currently executing.  This also generally indicates that  
  116.     //          a Master Table with that job name exists in the user schema.  Refer  
  117.     //          to any following error messages for clarification.  
  118.     // *Action: Select a different job name, or stop the currently executing job    
  119.     //          and re-try the operation (may require a DROP on the Master Table).  */  
  120.       
  121.     scott@CNMMBO> select table_name from user_tables where table_name like 'JOB%';  
  122.       
  123.     TABLE_NAME  
  124.     ------------------------------   
  125.     JOB_EXP  
  126.       
  127.     scott@CNMMBO> drop table job_exp;  
  128.     drop table job_exp  
  129.                *  
  130.     ERROR at line 1:  
  131.     ORA-00054: resource busy and acquire with NOWAIT specified  
  132.       
  133.     scott@CNMMBO> SELECT DISTINCT    object_name  
  134.       2                  || '   '  
  135.       3                  || locked_mode  
  136.       4                  || '   '  
  137.       5                  || ctime  
  138.       6                  || '   '  
  139.       7                  || c.SID  
  140.       8                  || '  '  
  141.       9                  || serial#  
  142.      10             FROM v$locked_object a, dba_objects b, v$lock c, v$session d  
  143.      11            WHERE a.object_id = b.object_id  
  144.      12              AND c.SID = a.session_id  
  145.      13              AND c.SID = d.SID;  
  146.       
  147.     OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL#   
  148.     -----------------------------------------------------------------------  
  149.     JOB_EXP   3   552   1075  799  
  150.       
  151.     scott@CNMMBO> alter system kill session '1075,799';  
  152.       
  153.     System altered.  
  154.       
  155.     scott@CNMMBO> drop table job_exp purge;   -->删除表之后再次进行导出  
  156.       
  157.     Table dropped.    
  158.   
  159. 9、使用视图监控datapump状态  
  160.     scott@CNMMBO> col owner_name format a15  
  161.     scott@CNMMBO> col operation format a15  
  162.     scott@CNMMBO> col state format a20  
  163.     scott@CNMMBO> select owner_name,job_name,operation,job_mode,state,degree from dba_datapump_jobs;  
  164.       
  165.     OWNER_NAME      JOB_NAME        OPERATION       JOB_MODE   STATE                    DEGREE  
  166.     --------------- --------------- --------------- ---------- -------------------- ----------  
  167.     SCOTT           JOB_EXP1        EXPORT          SCHEMA     EXECUTING                     1  
  168.   
  169. 10、使用下面的过程设定并行度  
  170.     DBMS_DATAPUMP.set_parallel (hand, 1);  
  171.           
  172. 11、上述操作所在的演示环境  
  173.     scott@CNMMBO> select * from v$version where rownum<2;  
  174.       
  175.     BANNER  
  176.     ----------------------------------------------------------------  
  177.     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简化管理

bodog信誉第一js bodog信誉第一js
原创粉丝点击
热门IT博客
蔡健雅 知乎蔡健雅 知乎
淘宝信用卡怎么申请淘宝信用卡怎么申请
export java home
闪电分期淘宝怎么用
penbeat专用笔淘宝
java 对外接口 案例
类似知乎天涯的网站
中万网络销售
淘宝专业秒杀电话
删除卷会删除数据吗
vmware桥接网络
dex编程
淘宝网评价规则
网络连接超时怎么处理
摄像头破解软件
板式家具下料软件
淘宝上的stussy 正品
h5页面设计软件
js 跨域图片转base64
我的梦想 知乎
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 天才学霸 冬夜风雪 妖尾之被动无敌冬夜雪草 妖尾之被动无敌 冬夜雪草 海棠散尽人未归 冬夜歌 白天和晚上都是冬夜 冬天成语 冬天诗句 冬天词语 冬天的成语 济南的冬天 冬天的诗句 冬天的柳叶 冬天 济南冬天 冬天作文 冬天古诗 冬天喝茶 冬天的词语 今年冬天 家乡的冬天 冬天句子 冬天英语 冬天的秘密 冬天的作文 冬天图片 冬天冷 今年冬天冷 冬天英文 冬天开花 冬天是 冬天图 作文冬天 描写冬天 冬天的英文 什么的冬天 冬天照片 冬天什么 关于冬天 冬天描写 形容冬天 冬天到了

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里