Some Datapump Scripts.

Thesee are some scripts I posted in OTN various times. I am trying to collate them together in this thread.

Script I: Exporting particular schema(s).

DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
BEGIN
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
                                    job_mode    => 'SCHEMA',
                                    remote_link => NULL,
                                    job_name    => 'SAUBHIK_EXPORT',
                                    version     => 'COMPATIBLE');

  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_expfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_logfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter(handle => l_dp_handle,
                                name   => 'SCHEMA_EXPR',
                                value  => 'IN (''HR'',''SAUBHIK'')');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error!');
    dbms_datapump.stop_job(l_dp_handle);
END;---------------------------------------------------------------------
Script II: Exporting particular tables with LIKE clause.

DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
BEGIN
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
                                    job_mode    => 'TABLE',
                                    remote_link => NULL,
                                    job_name    => 'SAUBHIK_EXPORT',
                                    version     => 'COMPATIBLE');

  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_expfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_logfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter(handle => l_dp_handle,
                                name   => 'NAME_EXPR',
                                value  => 'LIKE ''EMP%''');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error!');
    dbms_datapump.stop_job(l_dp_handle);
END;-------------------------------------------------------------------
SELECT * FROM user_datapump_jobs;

Script III: Exporting particular table.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    l_dp_handle    NUMBER;
  3    l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  4    l_job_state    VARCHAR2(30) := 'UNDEFINED';
  5    l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  6    l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
  7    l_tbl_name    VARCHAR2(30) :='EMP';
  8  BEGIN
  9    l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
 10                     job_mode    => 'TABLE',
 11                     remote_link => NULL,
 12                     job_name    => 'SAUBHIK_EXPORT',
 13                     version     => 'COMPATIBLE');
 14    DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
 15                  filename    => l_expfilename,
 16                  directory => 'SAUBHIK',
 17                  filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 18    DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
 19                  filename    => l_logfilename,
 20                  directory => 'SAUBHIK',
 21                  filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 22    dbms_datapump.metadata_filter(handle => l_dp_handle,
 23                     name   => 'NAME_EXPR',
 24                     value  =>'= '||''''||l_tbl_name||'''');
 25    dbms_datapump.start_job(l_dp_handle);
 26    dbms_datapump.detach(l_dp_handle);
 27  EXCEPTION
 28    WHEN OTHERS THEN
 29     dbms_datapump.stop_job(l_dp_handle);
 30     RAISE;
 31* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM user_datapump_jobs;

JOB_NAME               OPERATION              JOB_MODE                 STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SAUBHIK_EXPORT               EXPORT                  TABLE                 EXECUTING                     1       0         2

SQL> /

JOB_NAME               OPERATION              JOB_MODE                 STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SAUBHIK_EXPORT               EXPORT                  TABLE                 NOT RUNNING                 0       0         0

SQL> /

no rows selected

SQL>