EXPDP and DBMS_DATAPUMP equivalent commands

A quick reference of expdp command line parameters vs the DBMS_DATAPUMP equivalents

CLUSTER
dbms_datapump.start_job(xxx,xxx,xxx,cluster_ok=>0,xxx,xx); = 'N'
dbms_datapump.start_job(xxx,xxx,xxx,cluster_ok=>1,xxx,xx); = 'Y'
COMPRESSION
dbms_datapump.set_parameter(handle => l_dp_handle, name => 'COMPRESSION', value => 'ALL');
dbms_datapump.set_parameter(handle => l_dp_handle, name => 'COMPRESSION', value => 'DATA_ONLY');
dbms_datapump.set_parameter(handle => l_dp_handle, name => 'COMPRESSION', value => 'METADATA_ONLY');
CONTENT
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); ALL
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 0); DATA_ONLY
DBMS_DATAPUMP.DATA_FILTER(handle => h1, name => 'INCLUDE_ROWS', value => 0); METADATA_ONLY
DATA_OPTIONS
dbms_datapump.set_parameter(handle => l_dp_handle, name => 'DATA_OPTIONS', value => DBMS_DATAPUMP.KU$_DATAOPT_XMLTYPE_CLOB);
DIRECTORY
See dumpfile/logfile
DUMPFILE
  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.dmp',
                         directory => 'SCRATCH'
                       );
ENCRYPTION

dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION', value => 'ALL');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION', value => 'DATA_ONLY');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION', value => 'ENCRYPTED_COLUMNS_ONLY');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION', value => 'METADATA_ONLY');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION', value => 'NONE');
ENCRYPTION_ALGORITHM
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_ALGORITHM', value => 'AES128');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_ALGORITHM', value => 'AES192');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_ALGORITHM', value => 'AES256');
ENCRYPTION_MODE
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_MODE', value => 'DUAL');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_MODE', value => 'PASSWORD');
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_MODE', value => 'TRANSPARENT');
ENCRYPTION_PASSWORD
dbms_datapump.set_parameter(handle => h1, name => 'ENCRYPTION_PASSWORD', value => 'secret password');
ESTIMATE
dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
ESTIMATE_ONLY
  dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE_ONLY', value => 1);
EXCLUDE
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_EXPR', '<> (''SCHEMANAME'')');
  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                                'EXCLUDE_PATH_EXPR',
                                'IN (''INDEX'', ''SYNONYMS'',''GRANTS'',''STATISTICS'')');
FILESIZE
  DBMS_DATAPUMP.add_file(handle    => h1,
                         filename  => 'test.dmp',
                         directory => 'SCRATCH',filesize => '1024000',
                         reusefile => 1);
FLASHBACK_SCN
dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_SCN', value => nnnnnnnn);
dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_SCN', value => TIMESTAMP_TO_SCN( TO_TIMESTAMP( TO_CHAR( SYSDATE) )));
FLASHBACK_TIME
dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_TIME', value =>  'TO_TIMESTAMP( TO_CHAR( SYSDATE) )');
FULL
  h1 := DBMS_DATAPUMP.open(operation   => 'EXPORT',                                    job_mode    => 'FULL',                                    remote_link => NULL,                                    version     => 'LATEST');
HELP
n/a
INCLUDE
  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                                'SCHEMA_EXPR',
                                'IN (''SCHEMANAME'')');
JOB_NAME
  h1 := DBMS_DATAPUMP.open(operation   => 'EXPORT',                                    job_mode    => 'FULL',                                    remote_link => NULL,                                    version     => 'LATEST',job_name =>'MY JOB NAME');
LOGFILE
  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.log',
                         directory => 'SCRATCH',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                         reusefile => 1);
NETWORK_LINK
  h1 := DBMS_DATAPUMP.open(operation   => 'EXPORT',                                    job_mode    => 'FULL',                                    remote_link => 'DB_LINK_NAME',                                    version     => 'LATEST',job_name =>'MY JOB NAME');
NOLOGFILE
Do not specify a logfile
PARALLEL
DBMS_DATAPUMP.set_parallel(handle=>h1,degree=n);
PARFILE
n/a
QUERY
dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'WHERE col1 = 1', table_name => null, schema_name => null);
REMAP_DATA
dbms_datapump.data_remap(handle => h1, name => 'COLUMN_FUNCTION', table_name => 'TAB1', column => 'COL1', function => 'PUMPY.TESTPACKAGE.SETTESTID', schema => 'PUMPY');
REUSE_DUMPFILES
  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.dmp',
                         directory => 'SCRATCH',
                         reusefile => 1);

SAMPLE
dbms_datapump.data_filter(handle => h1, name => 'SAMPLE', value => '10, table_name => null, schema_name => null);
SCHEMAS
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''ALIGNE''');
SERVICE_NAME
DBMS_DATAPUMP.start_job(l_dp_handle,service_name=>'MY_SERVICE_NAME');
SOURCE_EDITION
dbms_datapump.set_parameter(handle => h1, name => 'SOURCE_EDITION', value => 'edition name');
STATUS
n/a
TABLES
dbms_datapump.metadata_filter(handle      => h1,                                name        => 'NAME_EXPR',                                value       => 'IN (''TEST'',''TEST2'')',                                object_type => 'TABLE');
TABLESPACES
dbms_datapump.metadata_filter(handle      => h1,                                name        => 'TABLESPACE_EXPR',                                value       => 'IN (''TBS1'',''TBS2'')',                                object_type => 'TABLESPACE');
TRANSPORTABLE
dbms_datapump.set_parameter(handle => h1, name => 'TRANSPORTABLE', value => 'ALWAYS');
dbms_datapump.set_parameter(handle => h1, name => 'TRANSPORTABLE', value => 'NEVER');
TRANSPORT_FULL_CHECK
dbms_datapump.set_parameter(handle => h1, name => 'TTS_FULL_CHECK', value => 0); N
dbms_datapump.set_parameter(handle => h1, name => 'TTS_FULL_CHECK', value => 1); Y
VERSION
h1 := DBMS_DATAPUMP.open(operation   => 'EXPORT',                                    job_mode    => 'FULL',                                    remote_link => 'DB_LINK_NAME',                                    version     => 'LATEST',job_name =>'MY JOB NAME');
 h1 := DBMS_DATAPUMP.open(operation   => 'EXPORT',                                    job_mode    => 'FULL',                                    remote_link => 'DB_LINK_NAME',                                    version     => 'COMPATIBLE',job_name =>'MY JOB NAME');
ACCESS_METHOD
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'DIRECT_PATH');
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'EXTERNAL_TABLE');
METRICS
DBMS_DATAPUMP.METADATA_FILTER(
HANDLE => HANDLR,
NAME => 'METRICS',
VALUE => 0
);
DBMS_DATAPUMP.METADATA_FILTER(
HANDLE => HANDLR,
NAME => 'METRICS',
VALUE => 1
);
No equivalent
dbms_datapump.set_parameter(handle => h1, name => 'USER_METADATA', value => 1); export create user statement in schema mode (only if datapump_exp_full_database role is granted)
dbms_datapump.set_parameter(handle => h1, name => 'USER_METADATA', value => 0); don't export create user statement in schema mode
TRACE
dbms_datapump.set_debug(debug_flags=>to_number('1FF0300','XXXXXXXXXXXXX'),version_flag=>1);
KEEP_MASTER
  dbms_datapump.set_parameter(handle => l_dp_handle, name => 'KEEP_MASTER', value => 1);
  dbms_datapump.set_parameter(handle => l_dp_handle, name => 'KEEP_MASTER', value => 0);


Comments

  1. Nuts10 April 2016 at 19:32

    nice work!!
    that's very handy.. thank you

    ReplyDelete
  2. Unknown28 December 2016 at 07:59

    Thank u, very usefull!!!

    ReplyDelete
  3. Anonymous12 September 2017 at 19:50

    Thank you!!! :)

    ReplyDelete
  4. Anonymous7 May 2018 at 02:41

    Very useful. Found slight error, with METRICS, the commands should be
    DBMS_DATAPUMP.SET_PARAMETER(
    HANDLE => HANDLR,
    NAME => 'METRICS',
    VALUE => 0
    );
    DBMS_DATAPUMP.SET_PARAMETER(
    HANDLE => HANDLR,
    NAME => 'METRICS',
    VALUE => 1

    ReplyDelete
  5. Unknown28 October 2019 at 09:49

    Very nice and useful information.

    ReplyDelete

Post a Comment