Export Import Datapump Basic Commands

For more exciting blogs on Oracle, please click the LINK

1. EXCLUDING TABLE PARTITIONS

JOB_NAME=INFRA_EXPORT
DIRECTORY=DUMP_DIR
DUMPFILE=Schema_%U.dmp
LOGFILE=Schema.log
SCHEMAS=INFRA
EXCLUDE=table_data:"IN ('INFRA_KEY_2005Q1','INFRA_KEY_2005Q2')"
FILESIZE=100G
PARALLEL=2
CONSISTENT=Y
COMPRESSION=ALL

2. EXCLUDE Tables:

JOB_NAME=INFRA_EXPORT
DIRECTORY=DUMP_DIR
DUMPFILE=Schema_%U.dmp
LOGFILE=Schema.log
SCHEMAS=INFRA
EXCLUDE=TABLE:"IN('INFRA_PROPERTY','INFRA_POOL')"
PARALLEL=2
CONSISTENT=Y

3. Takes privileges and roles :

JOB_NAME=INFRA_EXPORT
DIRECTORY=DUMP_DIR
DUMPFILE=Schema_%U.dmp
LOGFILE=Schema.log
FULL=y
INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE,GRANT
PARALLEL=2
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"

4. INCLUDE Tables:

JOB_NAME=INFRA_EXPORT
DIRECTORY=DUMP_DIR
DUMPFILE=Schema_%U.dmp
LOGFILE=Schema.log
SCHEMAS=INFRA
INCLUDE=TABLE:"IN('INFRA_TICKET_2G','INFRA_TICKET_3G')"
PARALLEL=2
CONSISTENT=Y

5. Query Level Export :

JOB_NAME=INFRA_EXPORT
DIRECTORY=DUMP_DIR
DUMPFILE=Tables_%U.dmp
LOGFILE=table.log
TABLES=INFRA.INFRA_TICKET_2G
QUERY=INFRA.INFRA_TICKET_2G:"WHERE lower(status) = 'success' and date < to_date ('01-Nov-2018', 'dd-Mon-yyyy')"

6. Take EXPDP bypassing system privileges

expdp \'/ as sysdba\' parfile=test.par &

7. Kill an already running expdp

expdp \'/ as sysdba\' attach=SYS_EXPORT_SCHEMA_01
expdp> kill_job

8. Check status of datapump job :

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

9. Check what import is doing :

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name,
to_char(LAST_ACTIVE_TIME,'MM/DD/YYYY HH24:MI:SS')
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

10. Generate DDL Script for your Export Dumpfile

impdp \'/ as sysdba\' DIRECTORY=IMPDP_DIR DUMPFILE=data.dmp sqlfile=ddl.sql &