Oracle Data Pump: Export & Import with expdp & impdp

When managing data in Oracle environments, two tools frequently emerge for backup, recovery, and migration operations: Data Pump and RMAN. While both are fundamental, their purposes and operational modes are distinct. Data Pump, with its expdp and impdp utilities, is the preferred choice for logical operations, allowing you to export and import schemas, tables, or entire databases at an object level. It’s ideal for migrations between different Oracle versions or for creating test environments. I’ve used Data Pump to migrate complex schemas between servers, drastically reducing downtime and simplifying data management for an organization with hundreds of gigabytes of databases.

Tested on: Oracle Database 19c · Oracle Linux 8 · October 2026

Data Pump vs. RMAN: Understanding Their Roles

Understanding the difference between Oracle Data Pump and RMAN (Recovery Manager) is crucial for choosing the right tool for the job. RMAN is a physical-level backup and recovery utility. It operates on data files, control files, and archivelogs of the database. It is the primary tool for full or incremental database backups, disaster recovery, and database duplication (cloning). RMAN is transaction-consistent and ensures data integrity at the block level.

Data Pump, on the other hand, is a tool for logical data unload and load. It works at the database object level (schemas, tables, indexes, stored procedures, etc.). It is ideal for:

• Schema or table migration: Moving objects from one database to another, even between different Oracle versions.
• Creating development/test environments: Duplicating a subset of a production database.
• Logical backups: Creating security copies of specific data that can then be imported into a different environment.
• Database consolidation: Merging multiple schemas from different databases into a single target.

The choice depends on the nature of the operation: if you need a physical backup for disaster recovery, use RMAN; if you need to move or duplicate data at a logical level, Data Pump is the solution.

Create Oracle Directory and Grant Permissions

Before you can use expdp or impdp, you must define a logical directory within the Oracle database that points to a physical directory on the server’s filesystem. This directory is where Data Pump will write dump files and logs during export, or from where it will read dump files during import. You cannot specify arbitrary paths directly in expdp/impdp commands; you must always refer to a predefined Oracle directory.

To create a directory, connect as a user with SYSDBA or CREATE ANY DIRECTORY privileges (e.g., system):

CREATE DIRECTORY dp_dir AS '/u01/export';

This command creates a logical directory named DP_DIR that points to the /u01/export path on the database server’s filesystem. Ensure that the physical path exists and that the operating system user running the Oracle process has read and write permissions on that directory.

Next, you must grant read and write permissions on this directory to the user(s) who will perform the expdp/impdp operations. For example, for the SYSTEM user:

GRANT READ, WRITE ON DIRECTORY dp_dir TO system;

Without these permissions, any attempt to run expdp or impdp will fail with errors related to directory access. Read also: Oracle DBA: Daily Checks for Real-World Output

Exporting a Schema

Exporting a complete schema is one of the most common operations with Data Pump. This includes all tables, indexes, views, triggers, stored procedures, and other objects associated with that schema. The expdp command offers many options to customize the export.

Here’s an example to export the APP schema to the dp_dir directory, generating dump files with names based on a pattern (app_%U.dmp) and a log file, using 4 parallel processes to speed up the operation:

expdp system/password@ORCLDB schemas=APP directory=dp_dir dumpfile=app_%U.dmp logfile=app_exp.log parallel=4

• system/password@ORCLDB: Credentials of a user with sufficient privileges.
• schemas=APP: Specifies the schema to export.
• directory=dp_dir: The Oracle directory defined previously.
• dumpfile=app_%U.dmp: The name of the dump file. %U is a placeholder that Data Pump replaces with a sequential number if parallelism is used, creating app_01.dmp, app_02.dmp, etc.
• logfile=app_exp.log: The file where all export information is logged.
• parallel=4: Instructs Data Pump to use 4 worker processes for the export, improving performance on large databases or those with many objects.

Exporting Specific Tables or Metadata Only

Data Pump offers high granularity, allowing you to export only specific objects or only their structure (metadata) without the data. This is useful for creating empty schemas or moving only a subset of tables.

To export only specific tables from a schema, use the tables option:

expdp system/password@ORCLDB tables=APP.ORDERS,APP.PRODUCTS directory=dp_dir dumpfile=orders_products.dmp logfile=orders_products_exp.log

If you only need the object structure (e.g., to replicate a database schema without the data), you can use the content=METADATA_ONLY option:

expdp system/password@ORCLDB schemas=APP directory=dp_dir dumpfile=app_metadata.dmp content=METADATA_ONLY logfile=app_metadata_exp.log

The content=DATA_ONLY option would do the opposite: export only data, without the object structure. The default setting is content=ALL, which exports both metadata and data.

Importing into a Different Schema (remap_schema)

One of the most powerful features of impdp is the ability to remap objects during import. This is crucial when moving schemas from one environment to another and wanting the source schema to be created with a different name in the target database, or when consolidating multiple schemas.

The remap_schema option allows you to specify that all objects belonging to a source schema be imported into a new target schema. For example, to import the APP schema from the dump file and create it as APP_TEST in the target database:

impdp system/password@ORCLDB directory=dp_dir dumpfile=app_%U.dmp remap_schema=APP:APP_TEST logfile=app_imp.log

This command will take all objects originally in the APP schema and create them in the APP_TEST schema in the current database. If APP_TEST does not exist, impdp will create it (if the importing user has the necessary privileges).

Moving Between Tablespaces (remap_tablespace)

Similar to remap_schema, the remap_tablespace option allows you to redirect objects from a source tablespace to a target tablespace during import. This is very useful for optimizing space allocation or adapting the imported schema to the target database’s configuration, which might have different tablespace names or a different storage strategy.

To remap objects that originally resided in TABLESPACE_OLD to TABLESPACE_NEW during import:

impdp system/password@ORCLDB directory=dp_dir dumpfile=app_%U.dmp remap_tablespace=TABLESPACE_OLD:TABLESPACE_NEW logfile=app_imp_ts.log

It is possible to combine remap_schema and remap_tablespace in a single command, offering maximum flexibility for complex migrations:

impdp system/password@ORCLDB directory=dp_dir dumpfile=app_%U.dmp remap_schema=APP:APP_TEST remap_tablespace=USERS:APP_DATA logfile=app_imp_full.log

This command will import the APP schema as APP_TEST and move all objects that were in the USERS tablespace to the new APP_DATA tablespace.

Parallelism and Compression

Performance is critical, especially with large databases. Data Pump offers two key options to optimize speed and space: parallelism and compression.

Parallelism (parallel): The parallel option (already seen in the expdp example) allows Data Pump to use multiple worker processes simultaneously to perform export/import operations. This significantly reduces the total time required. The number of parallel processes should be balanced with server resources (CPU, I/O) and the number of dump files. If you specify parallel=4 and dumpfile=app_%U.dmp, Data Pump will create 4 dump files and use 4 processes to write them.

Compression (compression): The compression option reduces the size of dump files, saving disk space and reducing transfer times. It is particularly useful for remote backups or archiving large amounts of data. Compression options include ALL (default), DATA_ONLY, METADATA_ONLY, NONE, ENABLE. ALL compresses both data and metadata.

expdp system/password@ORCLDB schemas=APP directory=dp_dir dumpfile=app_comp.dmp logfile=app_comp_exp.log parallel=4 compression=ALL

This combination can accelerate a terabyte-scale export/import from days to hours.

Reading the Log and Frequent ORA- Errors

The log file generated by expdp and impdp is your primary resource for monitoring the operation’s status and diagnosing any problems. It is essential to read it carefully, especially in case of errors.

The log contains:

• Details about the utility’s start (version, parameters).
• List of processed objects (tables, indexes, etc.).
• Performance statistics and final summary.
• Any ORA- error or WARNING that occurred.

Common ORA- errors and troubleshooting:

• ORA-31626: job does not exist / ORA-31633: unable to create master table: Often caused by permission issues on the Oracle directory or space problems on the SYSTEM tablespace (where the Data Pump job’s master table is created).
• Verify READ, WRITE permissions on the directory.
• Check free space in the SYSTEM tablespace.

• ORA-39002: invalid operation / ORA-39070: Unable to open the log file: Generally indicates that the physical path of the directory does not exist or that the Oracle user does not have write permissions on the physical directory of the operating system.
• Ensure that /u01/export exists and that the oracle user (or the user running the database) has rwx permissions on it.

• ORA-01031: insufficient privileges: The user specified for expdp/impdp does not have the necessary privileges to export/import the requested objects or to access the Oracle directory.
• Grant EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges (for full database import/export), or READ, WRITE permissions on the specific directory to the user.

• ORA-31693: Table data object "SCHEMA"."TABLE" failed to load/unload and is being skipped due to error:: A specific problem with a table. Check the log for the detailed ORA- error associated with this line. It could be a constraint issue, data type problem, or trigger issue.

When an error occurs, the log is your best friend. Look for ORA- messages and consult the official Oracle documentation for the specific code.

Common Errors and Troubleshooting

Beyond specific ORA- errors, there are some recurring issues with Data Pump:

1. Lack of disk space: Dump files can be very large. Ensure that the filesystem where the Oracle directory resides has sufficient space. A 1TB database export can generate a dump of hundreds of GB even with compression.
2. I/O performance issues: On systems with slow I/O, even with high parallelism, Data Pump operations can be slow. Monitor disk I/O during execution.
3. Character encoding: If the source and target environments have different character sets, you might encounter data corruption issues or errors during import. Verify NLS_CHARACTERSET in both databases.
4. Different Oracle versions: Although Data Pump can handle migrations between different versions, there are limitations. Generally, a dump created with version N can be imported into version N or N+1, but not into N-1. Always check compatibility in the official Oracle documentation. Read also: Oracle RMAN: Database Recovery After Failure

FAQ — Frequently Asked Questions

Can I use Data Pump for a full database backup for disaster recovery?

No, Data Pump performs a logical backup. It is not suitable for database-level disaster recovery. For that, you must use RMAN, which manages physical backups and allows restoration of the entire database, including control files and redo logs, ensuring block-level consistency and point-in-time recovery.

Is it possible to resume an interrupted Data Pump operation?

Yes, Data Pump supports restarting interrupted jobs. Each Data Pump job has a name. You can use the ATTACH command to reconnect to an existing job and resume the operation from where it left off, whether due to accidental or voluntary interruption. This is a significant advantage over the older exp/imp utilities.

What is the difference between FULL and SCHEMAS in expdp?

FULL=Y exports the entire database, including all schemas, tablespaces, roles, and profiles. It requires EXP_FULL_DATABASE privileges. SCHEMAS=schema_name exports only the objects within the specified schema. It is more granular and often preferred for migrations or backups of individual applications.

Can I exclude specific objects from export/import?

Yes, Data Pump offers EXCLUDE and INCLUDE options that allow you to specify which object types (e.g., EXCLUDE=TABLE:"IN ('LOG_TABLE')") or specific objects (e.g., INCLUDE=TABLE:"IN ('ORDER_ITEMS', 'CUSTOMERS')") you want to include or exclude from the operation. This is extremely useful for filtering unnecessary or sensitive data.

Do I need to restart the service after making changes?

No, Data Pump operations (expdp and impdp) do not require restarting the database service. They are client utilities that interact with the database through normal sessions. Changes to data or schemas occur in real-time and are immediately available, without the need for restarts.

Conclusions with Operational Takeaways

Oracle Data Pump is a powerful and flexible tool for logical data management in Oracle environments. Its ability to handle large data volumes with parallelism and compression makes it indispensable for DBAs and database architects. Always remember to correctly define Oracle directories, assign appropriate permissions, and leverage remap_schema and remap_tablespace options for smooth migrations. The log file is your compass for troubleshooting. Mastering expdp and impdp means reducing downtime, simplifying migration operations, and improving the robustness of logical backup strategies—a crucial skill in any enterprise environment.