Data Pump (Import & Export)

Oracle Data Pump is the standard tool for moving data and schema objects into and out of an Oracle database. It replaces the older exp and imp utilities and is dramatically faster — Data Pump runs inside the database server itself, reads and writes directly to server-side files, and can parallelise work across multiple streams. It is used for migrations between databases, schema cloning, refreshing test environments from production, and archiving historical data.

How Data Pump Works

Data Pump exports produce dump files — Oracle's proprietary binary format that captures both data and metadata (table definitions, indexes, constraints, grants, sequences). These files are written to and read from a directory object — a named Oracle object that points to a physical path on the database server's filesystem. You cannot specify a raw file path in a Data Pump command; you must always reference a directory object. The two executables are expdp (export) and impdp (import) — both are run from the operating system command line, not from SQL.

• Data Pump runs as a job inside the database — you can monitor, pause, and restart it without losing progress
• A directory object must exist and the user must have READ and WRITE privilege on it before exporting or importing
• Dump files can only be read by Data Pump — they are not plain text or CSV files
• Data Pump can export and import at four levels: full database, schema, tablespace, or individual table
• The PARALLEL parameter runs multiple worker processes simultaneously — speeds up large exports and imports significantly

Creating a Directory Object

Before running any Data Pump operation, a DBA must create a directory object pointing to an existing filesystem path on the database server and grant the exporting or importing user the appropriate privileges on it.

-- Create a directory object pointing to an existing server-side path
-- The path must already exist on the filesystem -- Oracle does not create it
CREATE OR REPLACE DIRECTORY dp_dir AS '/u01/app/oracle/datapump';
-- Grant the Data Pump user READ and WRITE on the directory
GRANT READ, WRITE ON DIRECTORY dp_dir TO dataplexa_store;
-- Verify the directory object exists
SELECT directory_name, directory_path
FROM   dba_directories
WHERE  directory_name = 'DP_DIR';

• The directory object name is stored in uppercase in DBA_DIRECTORIES — queries must match exactly
• The filesystem path must be accessible by the Oracle OS user (oracle) — if Oracle cannot write to it, the export fails immediately
• A single directory object can be reused for multiple export and import operations — there is no need to create a new one each time
• Oracle provides a default directory called DATA_PUMP_DIR pointing to a standard path — use it for quick tests but create a dedicated directory for production work

Exporting with expdp

All expdp commands are run from the OS command line as a user with the required database privileges. The most common export modes are schema-level (exports everything owned by a user) and table-level (exports specific tables). The LOGFILE parameter is always worth specifying — it captures the full output of the export job including any errors, written to the same directory object.

-- Export an entire schema -- run from the OS command line as oracle user
-- expdp system/password SCHEMAS=dataplexa_store DIRECTORY=dp_dir DUMPFILE=store_schema.dmp LOGFILE=store_export.log
-- Export specific tables only
-- expdp system/password TABLES=dataplexa_store.orders,dataplexa_store.customers DIRECTORY=dp_dir DUMPFILE=orders_customers.dmp LOGFILE=tables_export.log
-- Export with parallel workers and a compressed dump file (Enterprise Edition)
-- expdp system/password SCHEMAS=dataplexa_store DIRECTORY=dp_dir DUMPFILE=store_%U.dmp PARALLEL=4 COMPRESSION=ALL LOGFILE=store_parallel.log
-- Export only rows matching a condition (QUERY parameter)
-- expdp system/password TABLES=dataplexa_store.orders DIRECTORY=dp_dir DUMPFILE=orders_2024.dmp QUERY=dataplexa_store.orders:'"WHERE order_date >= DATE '"'"'2024-01-01'"'"'"' LOGFILE=orders_2024.log
-- Monitor a running Data Pump job from SQL
SELECT job_name, state, degree, attached_sessions
FROM   dba_datapump_jobs
WHERE  state != 'NOT RUNNING';

• The %U in the dumpfile name is replaced with a two-digit number — store_01.dmp, store_02.dmp etc — required when using PARALLEL to allow multiple files to be written simultaneously
• COMPRESSION=ALL compresses both data and metadata — typically reduces dump file size by 60–80% at a modest CPU cost
• DBA_DATAPUMP_JOBS shows all running and recently completed Data Pump jobs — useful for monitoring long-running exports
• The export log file is the first place to look if an export completes but objects are missing — warnings about skipped objects are written there

Importing with impdp

Importing reads from a dump file and recreates the objects and data in the target database. The most important import parameters are REMAP_SCHEMA — which redirects objects from one schema to another, essential when importing into a different user — and REMAP_TABLESPACE — which redirects objects to a different tablespace when the source and target have different tablespace layouts. Use TABLE_EXISTS_ACTION to control what happens when a table already exists in the target.

-- Full schema import into the same schema name
-- impdp system/password SCHEMAS=dataplexa_store DIRECTORY=dp_dir DUMPFILE=store_schema.dmp LOGFILE=store_import.log
-- Import into a different schema -- REMAP_SCHEMA redirects all objects
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=store_schema.dmp REMAP_SCHEMA=dataplexa_store:store_uat LOGFILE=uat_import.log
-- Import and redirect objects to a different tablespace
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=store_schema.dmp REMAP_SCHEMA=dataplexa_store:store_uat REMAP_TABLESPACE=users:uat_data LOGFILE=uat_import.log
-- Import specific tables only -- useful for restoring a single table from a full schema dump
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=store_schema.dmp TABLES=dataplexa_store.orders LOGFILE=orders_import.log
-- TABLE_EXISTS_ACTION options:
-- SKIP (default) -- skip the table if it exists
-- APPEND         -- add rows without truncating existing data
-- TRUNCATE       -- truncate then load
-- REPLACE        -- drop, recreate, then load
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=orders_customers.dmp TABLE_EXISTS_ACTION=APPEND LOGFILE=append_import.log

• REMAP_SCHEMA=source:target redirects every object from the source schema to the target — the target schema must already exist or Data Pump will create it
• REMAP_TABLESPACE=source:target is essential when the target database does not have tablespaces with the same names as the source
• TABLE_EXISTS_ACTION=APPEND is the safest option for adding data to an existing table — it does not modify existing rows
• You can import specific tables from a full database or full schema dump without needing to re-export — Data Pump reads only the relevant portions of the dump file

EXCLUDE and INCLUDE — Filtering What Is Exported

Large schemas often contain objects you do not need in a particular dump — statistics, audit trails, or specific tables that contain sensitive data. The EXCLUDE and INCLUDE parameters filter the export at the object type level, keeping dump files smaller and imports faster. Only one of the two can be used in a single operation.

-- Export the schema but exclude statistics (they will be regathered on import)
-- and exclude the audit log table which is large and not needed in UAT
-- expdp system/password SCHEMAS=dataplexa_store DIRECTORY=dp_dir DUMPFILE=store_no_stats.dmp EXCLUDE=STATISTICS EXCLUDE=TABLE:'"IN ('"'"'PRODUCT_PRICE_AUDIT'"'"')"' LOGFILE=exclude_export.log
-- Export only tables and their data -- exclude all other object types
-- expdp system/password SCHEMAS=dataplexa_store DIRECTORY=dp_dir DUMPFILE=tables_only.dmp INCLUDE=TABLE LOGFILE=include_export.log
-- Export only specific named tables using INCLUDE with a filter
-- expdp system/password SCHEMAS=dataplexa_store DIRECTORY=dp_dir DUMPFILE=core_tables.dmp INCLUDE=TABLE:'"IN ('"'"'ORDERS'"'"','"'"'CUSTOMERS'"'"','"'"'PRODUCTS'"'"')"' LOGFILE=core_export.log
-- Check the content of a dump file without importing it (SQLFILE writes DDL to a file)
-- impdp system/password DIRECTORY=dp_dir DUMPFILE=store_schema.dmp SQLFILE=dp_dir:schema_ddl.sql

• EXCLUDE=STATISTICS is almost always a good idea — statistics from the source database are rarely valid on a target with different data volumes, and excluding them reduces dump file size
• The SQLFILE parameter on impdp extracts all DDL from a dump file to a SQL script without executing anything — use it to preview or audit what an import would create
• INCLUDE and EXCLUDE accept object type names as defined in Oracle's Data Pump object type hierarchy — TABLE, INDEX, CONSTRAINT, GRANT, SEQUENCE, VIEW, PROCEDURE, and others
• Single and double quote nesting in EXCLUDE and INCLUDE filter expressions is notoriously tricky at the OS command line — consider using a parameter file instead

Lesson Summary

Practice Questions

Quiz

Next up — Oracle Cloud (OCI) — What Oracle Cloud Infrastructure is, how it differs from on-premises Oracle, and the key database services available including Autonomous Database.