DBMS_DATAPUMP示例

演示环境  

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

BANNER  

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

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  

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  

       l_dp_handle :=  

          DBMS_DATAPUMP.open (operation     => 'EXPORT'  

                            , job_mode      => 'SCHEMA'       

                            , remote_link   => NULL  

                            , job_name      => 'JOB_EXP1'  

                            , version       => 'LATEST');  

       DBMS_DATAPUMP. add_file (handle      => l_dp_handle  

               , filename    => 'scott_schema.dmp'  

               , directory   => 'DB_DUMP_DIR'  

                 , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);  

       DBMS_DATAPUMP. add_file (handle      => l_dp_handle  

                           , filename    => 'scott_schema.log'  

                   , directory   => 'DB_DUMP_DIR'  

                           , filetype    => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);  

       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'  <