Export job

Działania wykonujemy na użytkowniku system
Najpierw trzeba utworzyć folder do zapisu eksportów (domyślnie DATA_PUMP_DIR)

SQL> CREATE OR REPLACE DIRECTORY tmp AS '/tmp'; dla Linux
SQL> CREATE OR REPLACE DIRECTORY tmp AS 'c:\bck'; dla Windows

nadajemy uprawnienia do odczytu i zapisu

SQL> GRANT read, write ON DIRECTORY tmp TO system;
wszystkie skrypty dotyczą: eksportu zawierającego strukturę i dane
parametr version ustawiony jest na latest można również ustawić na compatible

Mamy do dyspozycji 4 podstawowe rodzaje Export Job

database- full export mode
schema mode np HR
table mode
tablespace mode

Skrypty PL/SQL

1.full export mode

DECLARE
  hd  NUMBER;
BEGIN
  hd := DBMS_DATAPUMP.OPEN(operation => 'EXPORT',job_mode => 'FULL',remote_link => NULL,job_name => 'FULL EXPORT MODE',version => 'LATEST' );
  DBMS_DATAPUMP.ADD_FILE( handle => hd, filename => 'Full Export Mode', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE  );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'COMPRESSION', value => 'NONE' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE', value => 'BLOCKS' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE_ONLY', value => 1 );
  DBMS_DATAPUMP.START_JOB(handle => hd);
END;
/

2.schema mode np HR

 DECLARE
  hd  NUMBER;
BEGIN
  hd := DBMS_DATAPUMP.OPEN(operation => 'EXPORT',job_mode => 'SCHEMA',remote_link => NULL,job_name => 'SCHEMA_EXPORT',version => 'COMPATIBLE' );
  DBMS_DATAPUMP.ADD_FILE( handle => hd, filename => 'schema_export', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE  );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'COMPRESSION', value => 'NONE' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'USER_METADATA', value => 1 );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE', value => 'BLOCKS' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE_ONLY', value => 1 );
  DBMS_DATAPUMP.METADATA_FILTER( handle => hd, name => 'SCHEMA_LIST', value => '''HR''' );
  DBMS_DATAPUMP.START_JOB(handle => hd);
END;
/

3.table mode

 DECLARE
  hd  NUMBER;
BEGIN
  hd := DBMS_DATAPUMP.OPEN(operation => 'EXPORT',job_mode => 'TABLE',remote_link => NULL,job_name => 'TABLE_EXPORT',version => 'LATEST' );
  DBMS_DATAPUMP.ADD_FILE( handle => hd, filename => 'table_export', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE  );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'COMPRESSION', value => 'NONE' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE', value => 'BLOCKS' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE_ONLY', value => 1 );
  DBMS_DATAPUMP.METADATA_FILTER( handle => hd, name => 'SCHEMA_LIST', value => '''HR''');
  DBMS_DATAPUMP.METADATA_FILTER( handle => hd, name => 'NAME_LIST', value => '''COUNTRIES'',''JOBS''' );
  DBMS_DATAPUMP.START_JOB(handle => hd);
END;
/

4.tablespace mode

 DECLARE
  hd  NUMBER;
BEGIN
  hd := DBMS_DATAPUMP.OPEN(operation => 'EXPORT',job_mode => 'TABLESPACE',remote_link => NULL,job_name => 'TABLESPACE_EXPORT',version => 'LATEST' );
  DBMS_DATAPUMP.ADD_FILE( handle => hd, filename => 'tablespace_export', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE  );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'COMPRESSION', value => 'NONE' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE', value => 'BLOCKS' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'ESTIMATE_ONLY', value => 1 );
  DBMS_DATAPUMP.METADATA_FILTER( handle => hd, name => 'TABLESPACE_LIST', value => '''SYSAUX'',''TEMP'',''UNDOTBS1'',''USERS''' );
  DBMS_DATAPUMP.START_JOB(handle => hd);
END;
/

Inną metodą zaplanowania job-a jest użycie Oracle SQL Developer'a 
Szczegółowy opis znajduje się na Blogu Oracle Base

Poniżej przykład wygenerowanego skryptu przy pomocy Oracle SQL Developer

set scan off
set serveroutput on
set escape off
whenever sqlerror exit
DECLARE
    h1 number;
    errorvarchar varchar2(100):= 'ERROR';
    tryGetStatus number := 0;
begin
    h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'FULL', job_name => 'EXPORT_JOB_SQLDEV_61', version => 'COMPATIBLE');
    tryGetStatus := 1;
    dbms_datapump.set_parallel(handle => h1, degree => 1);
    dbms_datapump.add_file(handle => h1, filename => 'EXPDAT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
    dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U.DMP', directory => 'DATA_PUMP_DIR', filesize => '100M',  filetype => 1);
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
    dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
    dbms_datapump.detach(handle => h1);
    errorvarchar := 'NO_ERROR';
EXCEPTION
    WHEN OTHERS THEN
    BEGIN
        IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
            DBMS_DATAPUMP.DETACH(h1);
        END IF;
    EXCEPTION
    WHEN OTHERS THEN
        NULL;
    END;
    RAISE;
END;
/