dbms_datapump schema level export

The ability to datapump out a schema pre a release cam up recently and I came up with this script to perform the job that can be called automatically from the release process:

It dumps out the current connected schema (overwriting if the file already exists) and returns a string with the state of the datapump - a value of 'COMPLETED' means all is OK

DECLARE
  l_dp_handle       NUMBER;
  l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
  l_job_state       VARCHAR2(30) := 'UNDEFINED';
  l_sts             KU$_STATUS;
  v_job_state       varchar2(4000);
BEGIN
  l_dp_handle := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    version     => 'LATEST');

DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'TpBackup1.dmp',
    directory => 'TEST2',
    reusefile => 1 );

DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'TpBackupLog.log',
    directory => 'TEST2',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
    reusefile => 1 );

  DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.WAIT_FOR_JOB (l_dp_handle,v_job_state);

DBMS_OUTPUT.PUT_LINE(v_job_state);

END;

0 comments:

Post a Comment