ExpDP / ImpDP
Jump to navigation
Jump to search
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;