Data Pump Oracle12c Tips

Oracle Data pump enables very high-speed movement of Data and metadata from one database to another.

Datapump Export == > Unload data and metadata into a set of operating system files called a dump file set

Datapump Import == > Load an export dump file set into a target database.

The following tips will help to tune these data pump exports and import process.

Tip #1

With the METRICS=Y option data pump helps quantify exactly how long Data Pump spends in each section and prints a line “Completed ….. in X seconds” after each section.

For example:

$ expdp dumpfile=DATA_PUMP_DIR:demo.dmp nologfile=y metrics=y userid=scott/tiger

Export: Release – Production

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release – 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting “SCOTT”.”SYS_EXPORT_SCHEMA_01″:  dumpfile=DATA_PUMP_DIR:demo.dmp nologfile=y metrics=y userid=scott/********
Estimate in progress using BLOCKS Tip…
Total estimation using BLOCKS Tip: 192 KB
Completed 1 PROCACT_SCHEMA objects in 3 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 4 TABLE objects in 4 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Completed  TABLE objects in  seconds
Completed 2 INDEX objects in 1 seconds
Completed 2 CONSTRAINT objects in 0 seconds
Completed  CONSTRAINT objects in  seconds
Completed 1 REF_CONSTRAINT objects in 9 seconds
. . exported “SCOTT”.”DEPT”                              5.937 KB       4 rows
. . exported “SCOTT”.”EMP”                               8.585 KB      14 rows
. . exported “SCOTT”.”SALGRADE”                          5.867 KB       5 rows
. . exported “SCOTT”.”BONUS”                                 0 KB       0 rows
Master table “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
Job “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully completed

Tip #2

Disable AWR (or Statspack) snapshots and the Automatic Maintenance Tasks while running Data Pump.  No need to run these auto jobs while data pump is still importing the DATA.


After importing re-enable:


Tip #3

Perform a SQL trace of the Data Pump process.  When exporting sometimes the database will spend an excessively long time exporting a certain object type.  This can be due to inefficient catalog queries.   A SQL trace will show the exact details of the queries being executed by expdp.  Problem internal catalog queries can sometimes be remedied with updated dictionary or fixed object statistics or with patches or other workarounds (such as EVENT parameters) as directed by internal bugs and other MOS notes.

Enable a trace of the Data Pump process by adding the TRACE=400300 expdp parameter.  My Oracle Support note 286496.1 explains the various Data Pump tracing options in detail.  However usually simply adding the Data Pump parameter TRACE=400301 will suffice.  The resulting trace files can then be analyzed using utilities such as TKPROF or ORASRP just like any other SQL Trace file.

Ensure that the user that connects to the database with the Export Data Pump or Import Data Pump utility, is a privileged user (i.e. a user who has the DBA role or the EXP_FULL_DATABASE resp. IMP_FULL_DATABASE role),

SQL> GRANT exp_full_database TO scott;

$ expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log TABLES=emp TRACE=480300

Also ensure that the init.ora/spfile initialization parameter MAX_DUMP_FILE_SIZE is large enough to capture all the trace information (or set it to unlimited which is the default value), e.g.:
— Ensure enough trace data can be written to the trace files:

SQL> CONNECT / as sysdba

———————————– ———– ——————————
max_dump_file_size string 10M

SQL> ALTER SYSTEM SET max_dump_file_size = unlimited SCOPE = both;

———————————— ———– ——————————
max_dump_file_size string UNLIMITED
The majority of errors that occur during a Data Pump job, can be diagnosed by creating a trace file for the Master Control Process (MCP) and the Worker Process(es) only, e.g.:
To run a Data Pump job with standard tracing:

— This results in two trace files in BACKGROUND_DUMP_DEST:

— Master Process trace file: <SID>_dm<number>_<process_id>.trc
— Worker Process trace file: <SID>_dw<number>_<process_id>.trc

% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott TRACE=480300

Tip #4

Adding object statics can be a time consuming part of the overall Data Pump import process.  However this time can be mitigated by pre-calculating and capturing them from a dry-run or practice migration.

The database’s optimizer statistics are version dependent.  Further objects are essentially reorganized by the Data Pump migration process and hence space and data distribution/density properties can be changed significantly by the migration process.  Therefore it doesn’t make sense to have Data Pump export and import optimizer the source database’s statistics as the process can be time consuming and they should be recalculated on the target database.

To save time, calculate object statistics prior to the actual migration window using migrated data from a dry-run.  Save these migration statistics to a table in the target databases using DBMS_STATS.EXPORT_*_STATS where they can be restored from after the actual migration takes place.

Tip #5

Data Pump supports importing directly through the network via the NETWORK_LINK parameter without dump file.  When used, only an import operation is required – there is no associated Data Pump export.  The NETWORK_LINK parameter must refer to a valid database link defined on the target database.

Importing through the NETWORK_LINK parameter can save a considerable amount of migration time as the table data no longer has to be written to disk, possibly copied from one system’s disk to another’s, and read from disk.

Some tables will not be applicable for Data Pump imports using the NETWORK_LINK parameter.  For example tables with LONG or LONG RAW data types.  These problematic tables must therefore be migrated using the traditional Data Pump approach of exporting to a dump file, copying that dump file, and importing by reading that dump file.

Also note that to use the NETWORK_LINK parameter the XDB and XML options must be installed in both the source and target databases.

See the examples below. Here we have two databases – SRCDB (source) and TGTDB(target)

SQL>  select name from v$database;





SQL> show user

SQL> select * from tab;

no rows selected

SQL> create table example_tab1 as select * from all_objects;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
EXAMPLE_TAB1                   TABLE


Configure TNS entry (File location: $ORACLE_HOME/network/admin/tnsnames.ora) for SRCDB in TGTDB database host. Entry as below:

(description =
(address =
(protocol = tcp)
(host =
(port = 1522)
(connect_data =
(server = dedicated)
(sid = SRCDB)
Test the connectivity using the tnsping utility

$ tnsping SRCDB

TNS Ping Utility for Solaris:

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol = tcp) (host = (port = 1522)) (connect_data = (server = dedicated) (sid = SRCDB)))
OK (20 msec)

Connect to TGTDB using sqlplus and create a database link to SRCDB with scott user

$ sqlplus

Enter user-name: scott/tiger

SQL> create database link SRCDB connect to scott identified by scott using ‘SRCDB’;

Database link created.

SQL> select * from tab@SRCDB;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
EXAMPLE_TAB1                   TABLE

Database link is working and ready from the database TGTDB to SRCDB

Import the scott schema of SRCDB database to TGTDB database without dumpfile.

$ impdp scott/tiger directory=exp_dir logfile=impnetworkscott.log network_link=SRCDB


Connected to: Oracle Database 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SCOTT”.”SYS_IMPORT_SCHEMA_01″:  scott/******** directory=exp_dir logfile=impnetworkscott.log network_link=SRCDB
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “SCOTT”.”EXAMPLE_TAB1″                      9507 rows
Job “SCOTT”.”SYS_IMPORT_SCHEMA_01″ completed.

SQL> select name from v$database;


SQL> show user

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
DEPT                           TABLE
EMP1                           TABLE
EMP2                           TABLE
EXAMPLE                      TABLE
EXAMPLE_P                  TABLE
T_EMP                         TABLE
TEST                           TABLE

8 rows selected.
Table EXAMPLE_TAB1 has been imported without dump file to TGTDB database!!!!

Welcome to DBA Stack

Welcome to – The Official DBA Source

We are so glad you stopped by. We are just getting started and we welcome your feedback. is a growing group of Database Administrators with a  mission to provide DBAs with valuable information that can be shared, expanded upon and put to practical use.  To get DBA Stack started, we’ll be focusing on a few subjects, such as Oracle, SAP, SQL Server 2016, and Mongo DB. However, we will be expanding to more and more as we grow, so we welcome all DBA related topics of interest.

We are also incorporating a FAQ section where you can ask your questions and have others from the community respond. More information coming soon. Have a question or want to help provide answers? Shoot us an email!

The main goal for is to form a community for DBAs to help each other through brainstorming, networking, sharing, and learning. DBA Stack couldn’t exists with out the contributions from our awesome community. Want your voice to be heard? We love contributions!

For DBAs, free time can be a very rare and precious thing. That is why we only want to post content that you care about, want to read, and can help make life a little easier. We believe no idea is too small, so let us know what you want to see.

We are still in our infancy stage, so thank you for your patience as we are getting started and building. New features and updates are added weekly, stay tuned.

Thank you for checking out.  We hope you will find it your go-to source for all things DBA!