Oracle Data Pump: The Complete expdp & impdp Export, Import and Migration Guide

You need to copy one schema from production to a test server. You need to move a database to new hardware. You need to hand a vendor a single table without giving them the whole database. For all of these, Oracle Data Pump is the tool - and it has quietly replaced the old exp/imp utilities for good reasons. This guide covers Data Pump end to end: how it actually works, the exports and imports you run every week, moving data directly between two databases with no dump file, parallel performance, and the handful of errors that trip up almost everyone the first time.

1. What Data Pump Is - and Why It Replaced exp/imp

Data Pump was introduced in Oracle 10g and is now the standard way to move logical data in and out of an Oracle database. The two command-line tools are expdp (export) and impdp (import). They look like the old exp/imp, but underneath they are a completely different, server-side architecture.

The key difference: the old tools ran on the client and streamed every row across the network to wherever you launched them. Data Pump runs inside the database, through the DBMS_DATAPUMP engine, using server processes that read and write files on the database server itself. That is why it is dramatically faster, can run in parallel, and can be stopped and restarted.

One consequence trips people up: a dump file created by the old exp cannot be read by impdp, and a Data Pump dump cannot be read by the old imp. They are separate formats. If you inherit an old .dmp from an ancient system, you need the matching old tool to read it.

2. The One Concept You Must Set Up First: Directory Objects

Because Data Pump writes on the server, it does not accept an operating-system path on your command line. Instead it writes to an Oracle directory object - a named database object that points at a real folder on the server, which the Oracle software owner must be able to write to.

-- As a DBA, create the directory object and grant access
CREATE OR REPLACE DIRECTORY dp_dir AS '/u01/dpdump';
GRANT READ, WRITE ON DIRECTORY dp_dir TO hr;

-- Confirm it and check the OS folder exists and is writable by the oracle user
SELECT directory_name, directory_path FROM dba_directories
WHERE directory_name = 'DP_DIR';

Forgetting this step is the number-one reason a first export fails. The folder must physically exist and be writable by the OS user that owns the Oracle processes, not by you.

3. Your Everyday Export: A Single Schema

The most common task is exporting one schema - for a backup before a risky change, or to copy it elsewhere.

expdp hr/password@ORCL \
  directory=dp_dir \
  dumpfile=hr_%U.dmp \
  logfile=hr_export.log \
  schemas=hr

A few things worth knowing here. The %U is a substitution wildcard - when you export in parallel Data Pump will produce hr_01.dmp, hr_02.dmp and so on. Always write a logfile and read it; the summary at the bottom tells you exactly what was and was not exported.

3.1 Full Database and Table-Level Exports

-- Whole database (needs DATAPUMP_EXP_FULL_DATABASE role)
expdp system/password directory=dp_dir dumpfile=full_%U.dmp \
  logfile=full_exp.log full=y parallel=4

-- Just two tables
expdp hr/password directory=dp_dir dumpfile=emp_dept.dmp \
  tables=hr.employees,hr.departments

-- Metadata only - structure, no rows (great for building an empty copy)
expdp hr/password directory=dp_dir dumpfile=hr_meta.dmp \
  schemas=hr content=metadata_only

The content parameter is underused: metadata_only gives you all the objects with no data, data_only gives rows with no DDL, and the default all gives both.

4. Importing - and the Parameters That Make It Useful

A plain import puts everything back where it came from. The real power of impdp is remapping, which lets you land the data somewhere different.

-- Import the HR schema INTO a different schema called HR_TEST,
-- and move its objects into a different tablespace
impdp system/password directory=dp_dir dumpfile=hr_%U.dmp \
  logfile=hr_imp.log \
  remap_schema=hr:hr_test \
  remap_tablespace=users:test_data \
  table_exists_action=replace

REMAP_SCHEMA is what you use to refresh a test schema from production without overwriting the original. REMAP_TABLESPACE handles the common case where test storage is laid out differently from production. And TABLE_EXISTS_ACTION decides what happens when a table is already there - skip, append, truncate, or replace. Choosing this consciously avoids nasty surprises.

If you only need to see what an import would do, generate the DDL instead of running it with sqlfile:

impdp system/password directory=dp_dir dumpfile=hr_%U.dmp \
  sqlfile=hr_ddl.sql schemas=hr
-- Writes all the CREATE statements to hr_ddl.sql, imports nothing.

5. Migrating Directly Between Two Databases - No Dump File

When you are moving a schema from an old server to a new one, you do not have to export to disk, copy the file, and import. Data Pump can pull the data straight across a database link with NETWORK_LINK. This often saves hours and a lot of disk.

-- On the TARGET database: create a link back to the SOURCE
CREATE DATABASE LINK src_db
  CONNECT TO hr IDENTIFIED BY password USING 'SOURCE_TNS';

-- Then import directly - no dumpfile parameter at all
impdp system/password directory=dp_dir \
  logfile=net_imp.log \
  network_link=src_db \
  remap_schema=hr:hr \
  parallel=4

This is one of the cleanest ways to migrate to new hardware or to consolidate a schema into a multitenant pluggable database. It also works across versions, letting you move from an older release up to a newer one, which pairs well with a planned 19c to 26ai upgrade.

6. Performance: Making Big Exports Finish in Time

For a large database the difference between a job that finishes overnight and one that overruns is usually parallelism. Data Pump scales well when you give it multiple workers and multiple files.

• PARALLEL: set it to roughly the number of CPU cores available, and always pair it with a %U dumpfile so each worker writes its own file. One file plus parallel is self-defeating.
• COMPRESSION: compression=all shrinks the dump substantially, trading CPU for far less I/O and disk - usually a win on modern servers.
• EXCLUDE: skip what you do not need. Excluding statistics (exclude=statistics) and regathering them after import is often faster than carrying them in the dump.
• ESTIMATE: run estimate_only=y first to size the job and confirm you have the disk space before you start.

expdp system/password directory=dp_dir dumpfile=big_%U.dmp \
  logfile=big_exp.log full=y \
  parallel=8 compression=all exclude=statistics

Data Pump jobs are also restartable. If a job dies, you can reattach to it by name with attach=JOB_NAME and resume, rather than starting over.

7. The Errors Everyone Hits First - and the Real Fix

Data Pump wraps most problems in a generic ORA-39002: invalid operation, which tells you nothing on its own. The useful message is always the line below it. The usual causes:

• ORA-39070 / cannot open logfile: the directory object does not exist, or the OS folder is missing or not writable by the oracle user. Fix the directory grant and the folder permissions.
• Target version older than source: a dump from a newer database will not import into an older one unless the export used version= to target the lower release. Set version at export time when you know the target is older.
• ORA-31626 / insufficient privileges: full exports and imports need the DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE roles. Schema-level work needs the directory grant.
• Out of space mid-job: the dump destination filled up, or the target tablespace could not extend. This is why estimate_only first is a good habit.

8. A Real Migration: 400 GB to New Hardware Over a Weekend

A manufacturing client needed their ERP database moved to new servers with minimal downtime. The dump-and-copy approach would have meant exporting 400 GB to disk, copying it across the network, and importing - too slow for the weekend window.

What we did: pre-created the schemas and tablespaces on the target, then ran a NETWORK_LINK import with parallel=8 and exclude=statistics, pulling directly from the old database. Statistics were regathered fresh after load. The direct pull removed the copy step entirely and the parallel workers used the new server's cores fully. The migration finished well inside the window, and because Data Pump validated object counts in its log, we had a clear record that everything arrived. This kind of logical move complements a physical strategy built on RMAN backup and recovery.

Frequently Asked Questions

What is the difference between Data Pump and the old exp/imp?

Data Pump (expdp/impdp) runs inside the database on the server and is far faster, parallel-capable, and restartable. The old exp/imp ran on the client and streamed rows over the network. Their dump file formats are not compatible - you cannot import an old exp dump with impdp or vice versa.

Why does my export fail with ORA-39002?

ORA-39002 is a generic wrapper; the real reason is the line below it. The most common cause is the directory object not existing or the operating-system folder not being writable by the Oracle software owner. Check the DIRECTORY object, the READ/WRITE grant, and the folder permissions first.

How do I import a schema into a different schema name?

Use REMAP_SCHEMA=source:target on the impdp command. For example remap_schema=hr:hr_test imports the HR objects into a schema called HR_TEST. Pair it with REMAP_TABLESPACE if the storage layout differs, which is the standard way to refresh a test environment from production.

Can I move data between two databases without a dump file?

Yes. Create a database link on the target pointing at the source, then run impdp with NETWORK_LINK set to that link and no dumpfile parameter. Data Pump pulls the data directly across the link, which is ideal for migrating to new hardware and can even move across different Oracle versions.

How do I make a large Data Pump export run faster?

The biggest lever is PARALLEL set to about the number of CPU cores, combined with a %U wildcard so each worker writes its own dump file. Adding compression=all reduces I/O, and excluding statistics then regathering them after import is often quicker than carrying them in the dump.