Data Pump Export Schema Within PL/SQL

I have one client where I don’t have access to the database server, but still need to perform exports to move metadata.  This can easily be accomplished using the Data Pump APIs.  Here’s an example of the script that I use, its the same script from The Data Pump API Utilities manual from Oracle.

DECLARE
  ind NUMBER;              
  h1 NUMBER;               
  percent_done NUMBER;     
  job_state VARCHAR2(30);  
  le ku$_LogEntry;         
  js ku$_JobStatus;        
  jd ku$_JobDesc;          
  sts ku$_Status;          
BEGIN

 h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'MYJOB','LATEST');

 DBMS_DATAPUMP.ADD_FILE(h1,'myexport.dmp','EXPORT_DIR');

 DBMS_DATAPUMP.ADD_FILE(handle=>h1,filename=>'myexport.log'
                         ,directory=>'EXPORT_DIR',
                        filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 

 DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''MY'')');

  DBMS_DATAPUMP.START_JOB(h1);

  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           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 js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

   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;

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END;
/

For a table level export simply replace the lines as follows:

h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'MYJOB','LATEST');

   h1 := DBMS_DATAPUMP.OPEN('EXPORT','TABLE',NULL,'MYJOB',LATEST');
DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''MY'')');

    DBMS_DATAPUMP.METADATA_FILTER(h1,'NAME_EXPR',IN 
             (''TABLE1'',''TABLE2''...)');
Advertisements

Tagged:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: