Oracle Data Pump cheat sheet: expdp, impdp, remaps, monitoring and the errors you'll hit

#Before you start

Data Pump runs on the database server, not on the client. Dump files are written to a directory object on the server, and the job runs as background processes (a master process DMnn and workers DWnn). Connect to the PDB service, not the CDB root, unless you really mean to export the root.

-- as a DBA in the PDB
CREATE OR REPLACE DIRECTORY dp_dir AS '/u01/app/oracle/dpdump/sales';
GRANT READ, WRITE ON DIRECTORY dp_dir TO app_admin;

-- full-database or other-schema exports/imports need these roles
GRANT DATAPUMP_EXP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE TO app_admin;

SELECT directory_name, directory_path FROM dba_directories;

The OS folder must exist and be writable by the oracle OS user on the database host. On RAC, use a shared file system, or keep PARALLEL jobs on one instance with CLUSTER=N.

Tip

Don't run Data Pump as / as sysdba. Oracle documents that SYSDBA should be used only when Oracle Support asks for it. Use a named DBA account with the Data Pump roles.

#Exports (expdp)

Put parameters in a parameter file. You avoid shell quoting problems with QUERY, INCLUDE and EXCLUDE, and you keep a record of what you ran.

#Schema export, consistent and parallel

schema_exp.par:

DIRECTORY=dp_dir
DUMPFILE=sales_%U.dmp
LOGFILE=sales_exp.log
SCHEMAS=SALES,SALES_STG
PARALLEL=4
FILESIZE=20G
FLASHBACK_TIME=SYSTIMESTAMP
EXCLUDE=STATISTICS
METRICS=YES
LOGTIME=ALL

expdp app_admin@//dbhost:1521/salespdb PARFILE=schema_exp.par

• FLASHBACK_TIME=SYSTIMESTAMP makes every table consistent to the same point in time. Without it, each table is consistent only to the moment it was exported. That breaks foreign keys in busy schemas.
• %U with PARALLEL lets each worker write its own file. Use at least as many files as the parallel degree.
• EXCLUDE=STATISTICS is faster. Gather fresh statistics on the target after the import.

Warning

PARALLEL greater than 1 needs Enterprise Edition. COMPRESSION=ALL and COMPRESSION=DATA_ONLY need the Advanced Compression option, and ENCRYPTION needs Advanced Security unless you are on a cloud service that includes it. Check your licences before using them. The default COMPRESSION=METADATA_ONLY is free.

#Other common exports

# a few tables, some rows only
TABLES=SALES.ORDERS,SALES.ORDER_LINES
QUERY=SALES.ORDERS:"WHERE order_date >= DATE '2026-01-01'"

# structure only (DDL, no rows)
CONTENT=METADATA_ONLY

# everything except one big audit table
EXCLUDE=TABLE:"IN ('AUDIT_LOG')"

# full database
FULL=Y

# dump readable by an older target release
VERSION=19

Check the size before exporting:

expdp app_admin@salespdb SCHEMAS=SALES ESTIMATE_ONLY=YES NOLOGFILE=YES

#Imports (impdp)

#Into a different schema and tablespace

schema_imp.par:

DIRECTORY=dp_dir
DUMPFILE=sales_%U.dmp
LOGFILE=sales_imp.log
SCHEMAS=SALES
REMAP_SCHEMA=SALES:SALES_UAT
REMAP_TABLESPACE=SALES_DATA:UAT_DATA
PARALLEL=4
TABLE_EXISTS_ACTION=SKIP
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
METRICS=YES
LOGTIME=ALL

impdp app_admin@//uathost:1521/uatpdb PARFILE=schema_imp.par

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y cuts redo during the load. It has no effect if the database is in FORCE LOGGING mode (always the case with Data Guard). Take a backup after the import if you use it.

#TABLE_EXISTS_ACTION

Caution

TRUNCATE and REPLACE destroy the existing data in the target. Double-check the connect string. Importing into production instead of UAT is the classic Data Pump outage.

#Useful import variations

# write the DDL to a script instead of running it - review before a real import
SQLFILE=sales_ddl.sql

# only rows, tables already exist
CONTENT=DATA_ONLY

# drop storage clauses so objects use target tablespace defaults
TRANSFORM=SEGMENT_ATTRIBUTES:N

# merge a partitioned table into a non-partitioned one
PARTITION_OPTIONS=MERGE

# rename a table on the way in
REMAP_TABLE=SALES.ORDERS:ORDERS_2026

#Without a dump file: NETWORK_LINK

Import straight from a source database over a database link. Nothing is written to disk.

CREATE DATABASE LINK src_sales CONNECT TO app_admin IDENTIFIED BY "********"
  USING '//srchost:1521/salespdb';

impdp app_admin@uatpdb NETWORK_LINK=src_sales SCHEMAS=SALES \
  REMAP_SCHEMA=SALES:SALES_UAT LOGFILE=dp_dir:net_imp.log

This works well for small and medium schemas. For very large data volumes, export to files with PARALLEL instead. Some data types, such as LONG columns, have restrictions over a network link; check the documentation for your release.

#Monitoring and controlling running jobs

-- jobs and their state
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

-- progress of the current work item
SELECT sid, opname, sofar, totalwork,
       ROUND(sofar / NULLIF(totalwork, 0) * 100, 1) AS pct, time_remaining
FROM v$session_longops
WHERE opname LIKE 'SYS_%' AND sofar <> totalwork;

Attach to a running job from any session (job names look like SYS_EXPORT_SCHEMA_01):

expdp app_admin@salespdb ATTACH=SYS_EXPORT_SCHEMA_01

Interactive commands at the Export> or Import> prompt:

Press Ctrl+C in a running client to get the interactive prompt without stopping the job.

#Orphaned jobs

A killed client or crashed instance can leave jobs in NOT RUNNING state, with master tables of the same name in the owner's schema.

SELECT owner_name, job_name, state
FROM dba_datapump_jobs
WHERE state = 'NOT RUNNING' AND attached_sessions = 0;

First try ATTACH followed by KILL_JOB. If the job can't be attached and is definitely not a stopped job you want to restart, drop its master table:

DROP TABLE app_admin.sys_export_schema_01 PURGE;

#Errors you'll hit

#Prevent it next time

• Keep parameter files in version control and name dump files and logs with the date and source. You'll know exactly what each file contains.
• Always import with SQLFILE first when the target isn't empty, and review the DDL.
• Gather statistics after imports with EXCLUDE=STATISTICS (DBMS_STATS.GATHER_SCHEMA_STATS), and recompile invalid objects with utlrp.sql or DBMS_UTILITY.COMPILE_SCHEMA.
• Data Pump is a logical copy, not a backup strategy. Use RMAN for recovery.