Datapump Expdp/Impdp Cheat Sheet

Prereqs

Directory:
CREATE DIRECTORY IMPEXPDIR AS '/mounts/transfer';

Quick Export

expdp parfile=exp.par
Example Parfile EXP.PAR
userid=system/password
directory=IMPEXPDIR
dumpfile=Out-%U.dmp
logfile=2012-04-27-dcdsprd_schemas_to_sdhdev1.log
schemas=SCOTT

Interrupting Job

Reattach

SQL>  select owner_name, state , JOB_NAME from DBA_DATAPUMP_JOBS ;

OWNER_NAME                     STATE                          JOB_NAME
—————————— —————————— ——————————
SYSTEM                             EXECUTING                      SYS_IMPORT_FULL_01
SQL> exit

$ impdp system/password attach=SYS_IMPORT_FULL_01

Interactive

^C
Export> kill
Are you sure you wish to stop this job ([yes]/no): yes

Hung process

select * From dba_datapump_jobs;

select owner_name, job_name,  
'exec DBMS_DATAPUMP.STOP_JOB ( DBMS_DATAPUMP.ATTACH('''||job_name||''','''|| owner_name ||''') );' as stop_cmd
from dba_datapump_jobs;

select OBJECT_NAME, CREATED 
FROM dba_objects, DBA_DATAPUMP_JOBS
WHERE owner=owner_name and object_name=job_name;

References

Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1]
External Table Enhancements in 11g - Compression and Encryption

How can we monitor a DataPump Job's Progress? (Doc ID 455720.1)

How To Diagnose And Troubleshoot Import Or Datapump Import Hung Scenarios (Doc ID 795034.1)

DataPump Export and Import Job And Attached Client Sessions (Doc ID 262557.1)

How To Use The New 11g Events++ Syntax For Easier SQL Tracing Of Datapump Operations? (Doc ID 813737.1)

Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp) (Doc ID 453895.1)

Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (Doc ID 286496.1)