Skip to main content

Data Pump Export Best Practices

Best Practices and recommended parameter settings for Oracle Data Pump export operations.

General Recommendations​

  • The recommended way to run a Data Pump export is in schema mode
  • Use a parameter file (PARFILE=xxx.par)
caution

Don't use sys as sysdba for the export

Data Pump Patching​

Apply the latest Data Pump Bundle Patch.

Consistency​

By default consistency is active on a per table level.

note

Make sure the undo tablespace is large enough because a consistent export requires undo blocks

To export the database with a consistent timestamp:

  • FLASHBACK_TIME=systimestamp

To export the database with a consistent SCN:

  • FLASHBACK_SCN=nnn

Legacy Mode:

  • CONSISTENT=y
tip

If the source database has Data Guard enabled the standby can be converted to a snapshot standby.
This database can then be used for the consistent export.

Diagnostics​

Always specify the LOGTIME=ALL and METRICS=YES parameters to log more timing/worker information and show the access_method per table in the logfile.

Here are some example logfiles with different diagnostics settings:

  • Logfile Example (Default Settings):
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCHEMA1"."TABLE1"                          5.117 KB       3 rows
  • Logfile Example (LOGTIME=ALL):
12-APR-23 12:46:40.660: Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
12-APR-23 12:46:40.828: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
12-APR-23 12:46:42.352: Processing object type SCHEMA_EXPORT/TABLE/TABLE
12-APR-23 12:46:48.775: . . exported "SCHEMA1"."TABLE1"                          5.117 KB       3 rows
  • Logfile Example (METRICS=YES):
W-1 Startup took 0 seconds
W-2 Startup took 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
W-1      Completed 1 TABLE objects in 0 seconds
W-1 . . exported "SCHEMA1"."TABLE1"                          5.117 KB       3 rows in 0 seconds using direct_path
  • Logfile Example (LOGTIME=ALL + METRICS=YES):
12-APR-23 11:09:49.519: W-1 Startup took 0 seconds
12-APR-23 11:09:50.883: W-2 Startup took 0 seconds
12-APR-23 11:09:52.593: W-2 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
12-APR-23 11:09:52.594: W-2      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
12-APR-23 11:09:52.734: W-2 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
12-APR-23 11:09:52.740: W-2      Completed 1 PROCACT_SCHEMA objects in 0 seconds
12-APR-23 11:09:55.705: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
12-APR-23 11:09:55.766: W-1      Completed 1 TABLE objects in 1 seconds
12-APR-23 11:10:01.275: W-1 . . exported "SCHEMA1"."TABLE1"                          5.117 KB       3 rows in 0 seconds using direct_path

Source DB Settings​

STREAMS_POOL_SIZE​

Data Pump uses Advanced Queuing so STREAMS_POOL_SIZE needs to be set to a minimum of 256 MB.

  • Check the current size:

    select current_size/1024/1024 as current_size_mb
    from v$sga_dynamic_components 
    where component='streams pool';

  • Set STREAMS_POOL_SIZE to minimum of 256 MB:

    alter system set streams_pool_size=256m scope=both;

Dictionary Statistics​

Make sure the dictionary stats (SYS, SYSTEM) are current before running the export:

exec dbms_stats.gather_schema_stats ('SYS');
exec dbms_stats.gather_schema_stats ('SYSTEM');

Overview of the most important and relevant Data Pump export parameters. Click here for examples.

SCHEMAS=n​

Schema export is the recommended method to run a Data Pump export and the schemas can be specified with this parameter.

Example:

expdp ... schemas=schema1,schema2,schema3

LOGTIME=ALL​

Always specify the logtime diagnostics parameter to log more timing information.

METRICS=YES​

Always specify the metrics diagnostics parameter to show more worker information and the access_method per table in the logfile.

PARALLEL=n​

note

This parameter is only valid for the Enterprise Edition

Use parallel processes to speed up the export operation. The number of processes depends on the actual number of cpus on the source system.

Degree of parallelism:

PlatformDegree
Cloud OCPU1 x OCPU count
Cloud ECPU.25 x ECPU count
On-prem x862 x CPU cores count
On-prem (Other)Depends

DUMPFILE=name%L.dmp​

For optimal performance use multiple dump files for the Data Pump export. The %L parameter will append a number based on the parallel settings to the filename.

  • Legacy/Old Format: dumpfile=name%U.dmp

EXCLUDE=STATISTICS​

Exclude optimizer statistics from the export. After the import the statistics should be gathered on the target db or transported from the source database with DBMS_STATS.

exclude=statistics

Optional Parameters​

This is a list of optional Data Pump parameters that can be set during export:

FILESIZE=nG​

To limit the size of the individual export dump files.

Example:

expdp ... dumpfile=mydump%L.dmp filesize=5G

Checksum​

note

The checksum will be calculated at the end of the export job. This can take some time depending on the size of the export files.

This is a new feature available starting from Oracle 21c and can be used to calculate a checksum during the export operation. There are 2 parameters that can be set: CHECKSUM and CHECKSUM_ALGORITHM. The default for CHECKSUM is no but is changed to yes automatically if CHECKSUM_ALGORITHM is specified. If CHECKSUM_ALGORITHM is not specified the default value is set to sha256.

Examples:

expdp ... checksum=yes checksum_algorithm=sha256
expdp ... checksum_algorithm=sha384
expdp ... checksum=yes

Compression​

Use compression if you have the advanced compression option licensed.
The recommended compression algorithm method is MEDIUM.

Example:

expdp ... compress=all compress_algorithm=medium

By default the compression parameter is set to METADATA_ONLY which does not require an Advanced Compression Option license because only metadata information is compressed. Setting the parameter to ALL or DATA_ONLY requires an extra license because of the actual data compression.

Valid Options:

COMPRESSION=[ALL | DATA_ONLY | METADATA_ONLY | NONE]

Multitenant​

To avoid any noisy neighbours if the resources are insufficient set the following parameters:

  • Restrict the number of concurrent Data Pump jobs for a PDB

    alter system set max_datapump_jobs_per_pdb=n;

  • Restrict the parallel degree setting for a Data Pump Job

    alter system set max_datapump_parallel_per_job=n container=all;

Examples​

expdp userid=system@pdb1 \
    schemas=SCHEMA1 \
    directory=DATA_PUMP_DIR \
    dumpfile=dump%L.dmp \
    logfile=expdp.log \
    filesize=1G \
    flashback_time=systimestamp \
    parallel=2 \
    exclude=statistics \
    logtime=all \
    metrics=yes