Oracle Data Pump, Done Right: A Migration You Can Prove

expdp full=y and hope is not a migration plan. It’s how you find out at 3 a.m. that the dump ran out of space, or the import invented a half-populated schema, or the “quick refresh” locked the source for an hour because nobody set a consistency point.

Data Pump is the workhorse that moves Oracle data — schema refreshes, database migrations, the lift into the cloud, the copy into a test environment. Almost every Oracle shop runs expdp and impdp; far fewer run them deliberately, with the handful of options that turn “it seemed to work” into “the row counts match and I can prove it.” This post is the architecture worth understanding, the flags that actually matter, the quoting trap that eats an afternoon, and — the part that ends the argument — a lab that exports a schema, drops it, imports it back, and asserts nothing was lost.

What Data Pump actually is

The first thing to internalize: Data Pump runs inside the database server, not on your client. When you launch expdp, the client just starts and monitors a job; the actual reading and writing is done by server processes, and the dump file is written to a path on the database server, referenced through a DIRECTORY object — never a client-side path. This is the big break from the legacy exp/imp tools (which streamed through the client and are deprecated for good reason): Data Pump is faster, parallelizable, restartable, and runs where the data is.

That architecture has direct consequences you plan around:

• You need a DIRECTORY. CREATE DIRECTORY dp_dir AS '/path/on/the/server' and grant read/write on it. The dump lives there, not on your laptop.
• It has modes. FULL=Y (whole database), SCHEMAS= (one or more schemas — the common one), TABLES=, and TABLESPACES=. Pick the narrowest mode that covers what you’re moving.
• It can skip the dump file entirely. With NETWORK_LINK=, impdp pulls straight from a source database over a database link — no dump file, no staging, no copying gigabytes twice. For a migration into a new database this is often the cleanest path.

flowchart LR
SRC["Source schema<br/>DPSHOP — 5,000 + 20,000 rows"] -->|expdp SCHEMAS=dpshop| DMP["shop.dmp<br/>in a DIRECTORY on the server"]
DMP -->|impdp| RT["Back into DPSHOP<br/>lossless round trip"]
DMP -->|"impdp REMAP_SCHEMA=dpshop:dpclone"| TGT["DPCLONE<br/>same rows, new name"]
SRC -. "impdp NETWORK_LINK (no dump file)" .-> TGT

The flags that actually matter

Most of Data Pump’s power is in a dozen parameters. These are the ones that decide whether a migration is clean:

• REMAP_SCHEMA=source:target — import the data into a different schema than it came from. This is the heart of most migrations and refreshes: export PROD_APP, import as TEST_APP. Its siblings REMAP_TABLESPACE and REMAP_DATAFILE do the same for storage when the target’s layout differs.
• PARALLEL=n — run the job with n worker processes. Essential for anything large, but with one catch people miss: give it multiple dump files with the %U wildcard (DUMPFILE=exp_%U.dmp) so the workers aren’t all fighting over one file. PARALLEL=8 against a single dumpfile is mostly waiting.
• INCLUDE / EXCLUDE / QUERY — move part of the data. INCLUDE=TABLE:"IN ('ORDERS')" takes one table; EXCLUDE=STATISTICS skips stats (regather them faster on the target); QUERY exports only rows matching a predicate. These are also the parameters with the notorious quoting problem — more on that below.
• CONTENT= — DATA_ONLY, METADATA_ONLY, or ALL. Migrate structure first and data later, or refresh data into an existing schema without touching its objects.
• TABLE_EXISTS_ACTION= — what impdp does when a table is already there: SKIP (the surprising default — it silently leaves existing tables alone), APPEND, TRUNCATE, or REPLACE. Getting this wrong is how a “refresh” ends up with yesterday’s data quietly untouched.
• FLASHBACK_TIME / FLASHBACK_SCN — export a consistent point in time. Without it, a long export of a busy database can capture different tables at different moments, and referential integrity in the dump is a coin flip. FLASHBACK_TIME=SYSTIMESTAMP costs nothing and buys you a consistent snapshot.
• ENCRYPTION / ENCRYPTION_PASSWORD — the dump file is plaintext data sitting on disk. If it contains anything sensitive, encrypt it, or you’ve just made an unprotected copy of your production data — the same “least-protected copy” problem that undoes encryption at rest.
• VERSION= — writing a dump a lower version can import (e.g., migrating down a release). Set it when the target is older than the source; forget it and the import simply refuses the file.

A minimal, deliberate schema export and remap looks like this:

# export one schema, consistent as of now, in parallel across 4 dump files
expdp system/*** DIRECTORY=dp_dir SCHEMAS=app \
      DUMPFILE=app_%U.dmp PARALLEL=4 FLASHBACK_TIME=SYSTIMESTAMP LOGFILE=exp.log

# import it into a DIFFERENT schema on the target
impdp system/*** DIRECTORY=dp_dir DUMPFILE=app_%U.dmp PARALLEL=4 \
      REMAP_SCHEMA=app:app_test TABLE_EXISTS_ACTION=REPLACE LOGFILE=imp.log

The quoting trap — use a parfile

The single most common way a Data Pump command fails is the shell mangling a filter. This looks right and almost never works from a normal shell:

expdp system/*** SCHEMAS=app INCLUDE=TABLE:"IN ('ORDERS')" DIRECTORY=dp_dir DUMPFILE=x.dmp

The double quotes, single quotes, and parentheses get eaten or reinterpreted by the shell before Data Pump ever sees them. The fix that always works is a parameter file — put the parameters in a text file, where no shell touches them:

# filt.par
SCHEMAS=app
INCLUDE=TABLE:"IN ('ORDERS')"
DIRECTORY=dp_dir
DUMPFILE=orders_only.dmp

expdp system/*** parfile=filt.par

If you take one operational habit from this post: any Data Pump job with an INCLUDE, EXCLUDE, or QUERY goes in a parfile. It’s the difference between a repeatable command and a guessing game.

Now prove it

The whole promise of a migration is that the data arrives intact. That’s checkable. Build a schema of known size — 5,000 customers and 20,000 orders — export it, drop it, and import it back:

after re-import: DPSHOP customers=5000  orders=20000
   -> lossless round trip: exact same 5,000 + 20,000 rows came back.

Then the actual migration move — import into a new schema with REMAP_SCHEMA:

impdp ... REMAP_SCHEMA=dpshop:dpclone
   DPCLONE customers=5000  orders=20000
   -> same data, new schema name.

And a selective export — INCLUDE just one table, via a parfile — lands only that table in the target:

DPONLY tables=[ORDERS]  orders=20000
   -> only ORDERS came across (no CUSTOMERS). INCLUDE filtered the export exactly.

Same rows, every time, with the counts asserted rather than eyeballed. That’s a migration you can hand to a change-approval board.

Don’t take my word for it — run it. The Data Pump lab stands up an Oracle Database Free container, builds the 5,000/20,000-row DPSHOP schema, and runs four drills: expdp the schema, drop it and impdp it back (asserting the row counts match), impdp with REMAP_SCHEMA into a new schema, and a parfile-driven INCLUDE export that asserts only the chosen table crosses over. If the round trip loses a row or the filter leaks a table, the run fails. It’s proven on every CI push.

What teams get wrong

• Command-line filters without a parfile. INCLUDE/EXCLUDE/QUERY typed at the shell get their quotes mangled. Put them in a parfile, always.
• PARALLEL against one dump file. The workers serialize on the single file. Use DUMPFILE=name_%U.dmp so each worker writes its own, and match the dumpfile count to PARALLEL.
• No consistency point on a busy source. A multi-minute export without FLASHBACK_TIME can capture tables at different SCNs, so foreign keys in the dump don’t line up. Always set FLASHBACK_TIME=SYSTIMESTAMP (or an SCN) for anything transactional.
• Trusting the default TABLE_EXISTS_ACTION. It’s SKIP — existing tables are left as-is, so a “data refresh” into a populated schema can silently do nothing. Choose TRUNCATE, APPEND, or REPLACE on purpose.
• A plaintext dump of sensitive data. The .dmp is readable data on disk and in whatever bucket you copy it to. Encrypt it (ENCRYPTION_PASSWORD) if it holds anything you’d protect in the database, and delete it when the move is done.
• Forgetting the things that aren’t rows. Grants, stored code, and statistics need to arrive too. Data Pump moves them by default, but people who script CONTENT=DATA_ONLY migrations often forget to also move privileges and to regather or import stats — and then the target is “there” but slow and half-broken.
• Copying a dump when NETWORK_LINK would do. Exporting to a file, copying it to the target host, and importing is three steps and double the storage. For a straight database-to-database move, impdp NETWORK_LINK= pulls directly and skips the file entirely.

Data Pump is the connective tissue of the cloud-migration story: once you’ve chosen which migration method fits and where the database should live, Data Pump is usually how the data actually moves — into Autonomous or a new home. Do it deliberately: the right mode, a consistency point, a parfile for every filter, parallelism with real dump files, and encryption on anything sensitive. Then prove the move the way that ends the argument — with the Data Pump lab, where a schema goes out, comes back, and the row counts still match to the last row.