Oracle : DBMS_DATAPUMP－兩隻小虎,一隻豬

Oracle DBMS_DATAPUMP

/*-----------------------------------------------------
CREATE DBMS_DATAPUMP TEST Table
-----------------------------------------------------*/
DROP TABLE RANGER.DEMO_DBMS_DATAPUMP;
CREATE TABLE RANGER.DEMO_DBMS_DATAPUMP
(
  ID    INTEGER,
  NAME  VARCHAR2(10)
);

ALTER TABLE RANGER.DEMO_DBMS_DATAPUMP ADD (
  CONSTRAINT DEMO_DBMS_DATAPUMP_PK 
  PRIMARY KEY
 (ID));
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (1,'A');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (2,'B');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (3,'C');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (4,'D');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (5,'E');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (6,'F');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (7,'G');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (8,'H');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (9,'I');
INSERT INTO  RANGER.DEMO_DBMS_DATAPUMP VALUES (10,'J');
COMMIT;

/*-----------------------------------------------------
Create DBMS_DATAPUMP full table export procedure
-----------------------------------------------------*/
CREATE OR REPLACE PROCEDURE RANGER.DATAPUMP_FULL_TABLE_EXP IS
   dp_handle   NUMBER;
   dp_job_name varchar2 (500) := 'DATAPUMP_EXP_JOB';
   ind         NUMBER;        -- loop index
   pct_done    NUMBER;        -- percentage complete
   job_state   VARCHAR2(30);  -- track job state
   le          ku$_LogEntry;  -- WIP and error messages
   js          ku$_JobStatus; -- job status from get_status
   jd          ku$_JobDesc;   -- job description from get_status
   sts         ku$_Status;    -- status object returned by get_status 
BEGIN
   dp_handle := DBMS_DATAPUMP.open(
      operation => 'EXPORT',
      job_mode  => 'TABLE',
      remote_link => NULL,
      job_name    => dp_job_name ,
      version => 'LATEST'
   );      
   DBMS_DATAPUMP.add_file(
      handle => dp_handle,
      filename => 'DATAPUMP_FULL_TABLE_EXP.dmp',
      directory => 'ARCHIVE_PUMP'
   );       
   DBMS_DATAPUMP.add_file(
      handle => dp_handle,
      filename => 'DATAPUMP_FULL_TABLE_EXP.log',
      directory => 'ARCHIVE_PUMP',
      filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
   );
   DBMS_DATAPUMP.metadata_filter(
      handle => dp_handle,
      name => 'SCHEMA_EXPR',
      value => 'IN (''RANGER'')'
   );
   DBMS_DATAPUMP.metadata_filter( 
      handle => dp_handle,
      name => 'NAME_EXPR',
      value => 'IN (''DEMO_DBMS_DATAPUMP'')');
   DBMS_DATAPUMP.start_job(dp_handle);
   -------------------------------------------------------------
   -- monitor job
   pct_done := 0;
   job_state := 'UNDEFINED';
   WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
      DBMS_DATAPUMP.GET_STATUS(dp_handle, DBMS_DATAPUMP.ku$_status_job_error +
         DBMS_DATAPUMP.ku$_status_job_status +
         DBMS_DATAPUMP.ku$_status_wip, -1, job_state, sts);

      js := sts.job_status;

      -- If the percentage done changed, display the new value
      IF js.percent_done != pct_done THEN
         dbms_output.put_line('*** Job percent done = ' ||
         to_char(js.percent_done));
         pct_done := js.percent_done;
      END IF;

      -- If any work-in-progress (WIP) or error messages 
      -- were received for the job, display them.
      IF (BITAND(sts.mask,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
         le := sts.wip;
      ELSE
         IF (BITAND(sts.mask,DBMS_DATAPUMP.ku$_status_job_error) != 0) THEN
         le := sts.error;
         ELSE
            le := NULL;
         END IF;
      END IF;

      IF le IS NOT NULL THEN
         ind := le.FIRST;
         WHILE ind IS NOT NULL LOOP
            DBMS_OUTPUT.put_line(le(ind).LogText);
            ind := le.NEXT(ind);
         END LOOP;
      END IF;
   END LOOP;

   -- Indicate that the job finished and detach from it.
   DBMS_OUTPUT.put_line('Job has completed');
   DBMS_OUTPUT.put_line('Final job state = ' || job_state);
   DBMS_DATAPUMP.detach(dp_handle);
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_DATAPUMP.stop_job(dp_handle);
END;

/*-----------------------------------------------------
Create DBMS_DATAPUMP filter data export procedure
-----------------------------------------------------*/

CREATE OR REPLACE PROCEDURE RANGER.DATAPUMP_FILTER_DATA_EXP IS
   dp_handle   NUMBER;
   dp_job_name varchar2 (500) := 'DATAPUMP_EXP_JOB';
   ind         NUMBER;        -- loop index
   pct_done    NUMBER;        -- percentage complete
   job_state   VARCHAR2(30);  -- track job state
   le          ku$_LogEntry;  -- WIP and error messages
   js          ku$_JobStatus; -- job status from get_status
   jd          ku$_JobDesc;   -- job description from get_status
   sts         ku$_Status;    -- status object returned by get_status 
BEGIN
   dp_handle := DBMS_DATAPUMP.open(
        operation => 'EXPORT',
        job_mode  => 'TABLE',
        remote_link => NULL,
        job_name    => dp_job_name ,
        version => 'LATEST'
        );      
    DBMS_DATAPUMP.add_file(
        handle => dp_handle,
        filename => 'DATAPUMP_FILTER_DATA_EXP.dmp',
        directory => 'ARCHIVE_PUMP'
        );       
    DBMS_DATAPUMP.add_file(
        handle => dp_handle,
        filename => 'DATAPUMP_FILTER_DATA_EXP.log',
        directory => 'ARCHIVE_PUMP',
        filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
        );
    DBMS_DATAPUMP.metadata_filter(
        handle => dp_handle,
        name => 'SCHEMA_EXPR',
        value => 'IN (''RANGER'')');
    DBMS_DATAPUMP.metadata_filter( 
        handle => dp_handle,
        name => 'NAME_EXPR',
        value => 'IN (''DEMO_DBMS_DATAPUMP'')');
    DBMS_DATAPUMP.DATA_FILTER(
        handle => dp_handle, 
        name => 'SUBQUERY', 
        value => 'WHERE ID > 5',
        table_name => 'DEMO_DBMS_DATAPUMP' ,
        schema_name => 'RANGER' 
        );    
   DBMS_DATAPUMP.start_job(dp_handle);
   -------------------------------------------------------------
   -- monitor job
   pct_done := 0;
   job_state := 'UNDEFINED';
   WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
      DBMS_DATAPUMP.GET_STATUS(dp_handle, DBMS_DATAPUMP.ku$_status_job_error +
         DBMS_DATAPUMP.ku$_status_job_status +
         DBMS_DATAPUMP.ku$_status_wip, -1, job_state, sts);

      js := sts.job_status;

      -- If the percentage done changed, display the new value
      IF js.percent_done != pct_done THEN
         dbms_output.put_line('*** Job percent done = ' ||
         to_char(js.percent_done));
         pct_done := js.percent_done;
      END IF;

      -- If any work-in-progress (WIP) or error messages 
      -- were received for the job, display them.
      IF (BITAND(sts.mask,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
         le := sts.wip;
      ELSE
         IF (BITAND(sts.mask,DBMS_DATAPUMP.ku$_status_job_error) != 0) THEN
         le := sts.error;
         ELSE
            le := NULL;
         END IF;
      END IF;

      IF le IS NOT NULL THEN
         ind := le.FIRST;
         WHILE ind IS NOT NULL LOOP
            DBMS_OUTPUT.put_line(le(ind).LogText);
            ind := le.NEXT(ind);
         END LOOP;
      END IF;
   END LOOP;

   -- Indicate that the job finished and detach from it.
   DBMS_OUTPUT.put_line('Job has completed');
   DBMS_OUTPUT.put_line('Final job state = ' || job_state);
   DBMS_DATAPUMP.detach(dp_handle);
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_DATAPUMP.stop_job(dp_handle);
END;

/*-----------------------------------------------------
Query DBMS_DATAPUMP Staus
-----------------------------------------------------*/
SELECT * FROM DBA_DATAPUMP_JOBS

Reference
http://www.psoug.org/reference/dbms_datapump.html
http://www.psoug.org/reference/datapump.html
http://www.orafaq.com/forum/t/78831/2/
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php#DataPumpAPI
http://download-uk.oracle.com/docs/cd/B13789_01/server.101/b10825/dp_export.htm#i1006376
http://journal.suteki.nu/2009/04/03/how-to-stop-or-kill-data-pump-jobs-in-oracle-the-correct-wa/
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_api.htm
http://download.oracle.com/docs/cd/B13789_01/server.101/b10825/dp_api.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_api.htm#i1008009
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_datpmp.htm#sthref1783