ExpDP / ImpDP

Export / Import

Commonly used parameters for expdp/impdp:

expdp <username> dumpfile=export.dmp logfile=export_exp.log directory=data_pump_dir schemas=user1 flashback_time=systimestamp
impdp <username> dumpfile=export.dmp logfile=export_imp.log directory=data_pump_dir schemas=user1 remap_schema=user1:newuser1

Create only the list of DDLs in the exportfile:

impdp <username> dumpfile=export.dmp logfile=export_sql.log directory=data_pump_dir sqlfile=ddl.sql

Kill datapump job

expdp <username> attach=<job>
stop_job=immediate / kill_job

Table cleanup:

SELECT owner_name, job_name, operation, job_mode, state
  FROM dba_datapump_jobs;

SELECT o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
   
DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;