Oracle Data Pump Export Utility

Oracle Data Pump replaced the older ‘exp’ and ‘imp’ utilities.

Create Data Pump Directory in the source DB host specifying the OS directory where the export will unload the data into a .dmp file

You can skip this step, and use an existing Directory.

SQL> CREATE DIRECTORY schema_refresh AS '/staging/tmpdir/dwdb';

Directory created.

SQL> SELECT directory_name, directory_path from all_directories;

…

SCHEMA_REFRESH

/staging/tmpdir/dwdb

I prefer to use a parameter file with expdp.

Create a parameter file to use with expdp; there a number of options to chose from, below basic options:

$ touch schema_refresh.par

$ vi  schema_refresh.par
" schema_refresh.par" [New file]
DUMPFILE= schema_refresh.dmp
DIRECTORY= SCHEMA_REFRESH
SCHEMAS=whse
EXCLUDE=TABLE:"IN ('USAGE_CONCURRENT_REQUESTS')"

 Run expdp command using the parameter file created in the step above. Preferably, and as per Oracle, do not run it ‘AS SYSDBA’.

$ expdp parfile= schema_refresh.par

…..
Export: Release 11.2.0.3.0 - Production on Mon Jan 21 15:31:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: whse
Password: *****
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
…….
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
….

While expdp is running, CTRL+C will allow one to make a number of changes to the job, such as:

Export> STATUS                {To check status of the job}

Export> KILL_JOB             {To kill the job}
Export> PARALLEL=16    {Assign number of workers to the job}
Export> EXIT_CLIENT      {Exit the client but job continues to run}

From another session, one can attach to a running expdp job:

$ expdp ATTACH=SYS_EXPORT_SCHEMA_01
….
Export: Release 11.2.0.3.0 - Production on Mon Jan 21 16:09:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: whse
Password: *****
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_01