Oracle Data Pump: export and import schemas with expdp and impdp

Data Pump (expdp and impdp) is Oracle’s logical export and import tool. It writes table data and object definitions into dump files that another Oracle database can load. Typical uses: copy a schema from production to test, move an application to a new server or version, or keep an extra logical copy next to RMAN backups. It does not replace RMAN for disaster recovery.

Data Pump runs inside the database server. The client tools only start and watch a job; dump files are written and read by the database on the server’s file system, through a directory object.

1. Create a directory object

Create the operating-system directory first: Oracle does not check that the path exists. Then, as a DBA connected to the PDB:

CREATE DIRECTORY dp_dir AS '/u01/app/oracle/dpdump/shop';
GRANT READ, WRITE ON DIRECTORY dp_dir TO appuser;

Privileged users can also use the default directory object DATA_PUMP_DIR. A user without the DATAPUMP_EXP_FULL_DATABASE role can export only their own schema; importing other schemas needs DATAPUMP_IMP_FULL_DATABASE on the target.

2. Export a schema

Connect to the PDB’s service. The schema mode is the default, but naming it makes the command clear. Oracle advises not to run Data Pump as SYSDBA unless Oracle Support asks you to.

expdp [email protected]:1521/orclpdb1.example.com \
  SCHEMAS=appuser DIRECTORY=dp_dir \
  DUMPFILE=appuser_%U.dmp LOGFILE=appuser_exp.log \
  PARALLEL=2 FLASHBACK_TIME=systimestamp

• %U expands to 01, 02 and so on, so parallel workers can each write their own file.
• FLASHBACK_TIME=systimestamp makes the export consistent as of its start time, which matters when the application keeps writing.
• FULL=YES exports the whole database (needs DATAPUMP_EXP_FULL_DATABASE); TABLES=appuser.orders exports single tables.
• CONTENT=METADATA_ONLY exports only definitions, no rows.

Use a parameter file for anything with quotes

Filters such as EXCLUDE, INCLUDE, QUERY and FLASHBACK_TIME="TO_TIMESTAMP(...)" contain quotes that shells mangle. Oracle recommends putting them in a parameter file:

SCHEMAS=appuser
DIRECTORY=dp_dir
DUMPFILE=appuser_tables.dmp
LOGFILE=appuser_tables.log
INCLUDE=TABLE:"IN ('ORDERS', 'CUSTOMERS')"

expdp [email protected]:1521/orclpdb1.example.com PARFILE=appuser_tables.par

3. Copy the files and import

Copy the dump files into the directory behind a directory object on the target server, create the same directory object there, then import. REMAP_SCHEMA loads objects into a different user, and REMAP_TABLESPACE into a different tablespace:

impdp [email protected]:1521/testpdb.example.com \
  DIRECTORY=dp_dir DUMPFILE=appuser_%U.dmp LOGFILE=appuser_imp.log \
  REMAP_SCHEMA=appuser:appuser_test \
  REMAP_TABLESPACE=app_data:test_data

TABLE_EXISTS_ACTION decides what happens when a table already exists: SKIP (the default), APPEND, TRUNCATE or REPLACE.

Warning: TRUNCATE deletes the existing rows and REPLACE drops and recreates the table. Before using either, check the target connection string and run the import with SQLFILE first to see what it would do:

impdp [email protected]:1521/testpdb.example.com \
  DIRECTORY=dp_dir DUMPFILE=appuser_%U.dmp SQLFILE=dp_dir:preview.sql

With SQLFILE, impdp writes the DDL it would run into preview.sql and changes nothing in the database.

4. Check the result

• Read the log file end to end; failed objects are reported with an ORA- code, and the last lines tell you whether the job completed with errors.
• Compare object counts: SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = 'APPUSER_TEST' GROUP BY object_type.
• Recompile invalid objects and check grants to roles or users that do not exist on the target.

Official documentation

• Oracle Database 19c Utilities: Data Pump Export
• Oracle Database 19c Utilities: Data Pump Import
• Oracle Database 19c SQL Reference: CREATE DIRECTORY