Data Pump: Export and Import

43.1 Concepts

Server-side, high-speed logical export/import (expdp / impdp) using DBMS_DATAPUMP. Replaces the legacy exp/imp.

Requires a DIRECTORY object. Master table, worker processes, parallelism.

43.2 Export

expdp system@pdb1 DIRECTORY=dp_dir DUMPFILE=hr_%U.dmp LOGFILE=hr_exp.log SCHEMAS=hr PARALLEL=4 COMPRESSION=ALL
expdp system@pdb1 DIRECTORY=dp_dir DUMPFILE=full.dmp FULL=Y
expdp hr@pdb1 DIRECTORY=dp_dir DUMPFILE=emp.dmp TABLES=employees,departments QUERY=employees:"WHERE department_id=50"
expdp system@pdb1 DIRECTORY=dp_dir DUMPFILE=meta.dmp SCHEMAS=hr CONTENT=METADATA_ONLY
expdp system@pdb1 ... FLASHBACK_TIME=SYSTIMESTAMP     # consistent export
expdp system@pdb1 ... ENCRYPTION=ALL ENCRYPTION_PASSWORD=*** EXCLUDE=STATISTICS
expdp ... ESTIMATE_ONLY=Y

43.3 Import

Interactive mode (Ctrl+C or ATTACH=job_name): STATUS, STOP_JOB, START_JOB, KILL_JOB, PARALLEL=n.

Use cases: migrations, schema refreshes, cross-platform/version moves (VERSION=), Transportable Tablespaces and Full Transportable Export/Import.

Data Pump to and from object storage (CREDENTIAL=, 21c/ADB).

impdp system@pdb2 DIRECTORY=dp_dir DUMPFILE=hr_%U.dmp SCHEMAS=hr REMAP_SCHEMA=hr:hr_test REMAP_TABLESPACE=users:app_data PARALLEL=4
impdp system@pdb2 ... TABLE_EXISTS_ACTION=REPLACE        # SKIP | APPEND | TRUNCATE | REPLACE
impdp system@pdb2 ... SQLFILE=ddl.sql                    # extract DDL only
impdp system@pdb2 ... NETWORK_LINK=src_link SCHEMAS=hr   # no dump file
impdp system@pdb2 ... TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
impdp ... INCLUDE=TABLE:"IN ('EMPLOYEES')" EXCLUDE=INDEX