Datapump Expdp/Impdp Cheat Sheet

Submitted by dave on Fri, 04/27/2012 - 15:15

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;

Querying Status Directly

-- Table/Index Status
SELECT PROCESS_ORDER, OBJECT_LONG_NAME, 
       PARTITION_NAME, 
       CASE PROCESSING_STATE
            WHEN 'E' THEN 'Estimated'
            WHEN 'R' THEN 'Retrieved (Exported)'
            WHEN 'W' THEN 'Written (Imported)'
            WHEN 'X' THEN 'Removed'
            END AS PROCESSING_STATE, 
       CASE PROCESSING_STATUS
            WHEN 'C' THEN 'Current'
            WHEN 'F' THEN 'Failure'
       END AS STATUS
From sys.SYS_IMPORT_TABLE_01 
WHERE OBJECT_LONG_NAME IS NOT NULL
  AND PROCESS_ORDER > 0
ORDER BY PROCESS_ORDER;

https://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/oow2011_dp_mastering.pdf
https://www.doag.org/formes/pubfiles/4164572/2012-K-DB-Dean_Gagne-Oracle_Data_Pump_Internals-Praesentation.pdf

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)