A Step-by-Step Guide to Backing Up Oracle Databases with Data Pump (expdp & impdp)

 How to Back Up an Oracle Database Using Data Pump (expdp & impdp)

Oracle Data Pump is a high-performance utility for exporting (expdp) and importing (impdp) data and metadata between Oracle databases. It enables fast, flexible and secure data movement for tasks like database backups, migrations and schema transfers. expdp creates dump files of database objects, while impdp restores them into a target database. Data Pump supports parallel execution, fine-grained filtering and remapping options, making it a powerful tool for DBAs handling large-scale Oracle environments.

Oracle Data Pump supports a variety of export and import modes:

Full database

Specific schemas

Individual tables

On the database server, if OS authentication is properly configured you can issue

expdp \"/ as sysdba\"

and Data Pump will not require you to enter the SYS password, it connects via OS authentication.

Export Process

Before taking the export create a directory in the database with the path which will be used to store the exported dump file.

SQL> CREATE DIRECTORY <directory_name> AS '/path/to/store/your/files';

If the directory name already exist you can either use a different directory name or replace it by running the following query:

SQL> CREATE OR REPLACE DIRECTORY <directory_name> AS '/path/to/store/your/files';

Full Database Export:

expdp <username>/<password>@<database> DIRECTORY=orcl_full DUMPFILE=expdp_fulldb.dmp LOGFILE=FULL_EXP.LOG FULL=YES

expdp \"/ as sysdba\" DIRECTORY=orcl_full DUMPFILE=expdp_fulldb.dmp LOGFILE=FULL_EXP.LOG FULL=YES

Taking pdb exports using expdp \"/ as sysdba\" method:

expdp \"sys/<sys-password>@<pdb-name> as sysdba\" DIRECTORY=orcl_full DUMPFILE=expdp_fulldb.dmp LOGFILE=FULL_EXP.LOG FULL=YES

Specific Schema Export:

expdp <username>/<password>@<database> DIRECTORY=exp_schema DUMPFILE=expdp_schema.dmp LOGFILE=expdp_schema.log SCHEMAS=<schema>

expdp \"/ as sysdba\" DIRECTORY=exp_schema DUMPFILE=expdp_schema.dmp LOGFILE=expdp_schema.log SCHEMAS=<schema>

Taking schema exports from a pdb using expdp \"/ as sysdba\" method:

expdp \"sys/<sys-password>@<pdb-name> as sysdba\" DIRECTORY=exp_schema DUMPFILE=expdp_schema.dmp LOGFILE=expdp_schema.log SCHEMAS=<schema>

Individual Table Export:

expdp <username>/<password>@<database> DIRECTORY=exp_table DUMPFILE=expdp_table.dmp LOGFILE=expdp_table.log TABLES=<schema>.<table_name>

expdp \"/ as sysdba\" DIRECTORY=exp_table DUMPFILE=expdp_table.dmp LOGFILE=expdp_table.log TABLES=<schema>.<table_name>

Taking table exports from a pdb using expdp \"/ as sysdba\" method:

expdp \"sys/<sys-password>@<pdb-name> as sysdba\" IRECTORY=exp_table DUMPFILE=expdp_table.dmp LOGFILE=expdp_table.log TABLES=<schema>.<table_name>

Import Process

Create a directory in the database pointing to the path where the exported dump files are located.

SQL> CREATE DIRECTORY <directory_name> AS '/path/to/the/dump/file/location';

OR

SQL> CREATE OR REPLACE DIRECTORY <directory_name> AS '/path/to/the/dump/file/location';

Full Database Import:

impdp <username>/<password>@<database> DIRECTORY=dump_dir DUMPFILE=expdp_fulldb.dmp LOGFILE=expdp_fulldb.log FULL=YES

impdp \"/ as sysdba\" DIRECTORY=dump_dir DUMPFILE=expdp_fulldb.dmp LOGFILE=expdp_fulldb.log FULL=YES

Doing pdb imports using impdp \"/ as sysdba\" method:

impdp \"sys/<sys-password>@<pdb-name> as sysdba\" DIRECTORY=dump_dir DUMPFILE=expdp_fulldb.dmp LOGFILE=expdp_fulldb.log FULL=YES

Specific Schema Import:

impdp <username>/<password>@<database> DIRECTORY=imp_schema DUMPFILE=expdp_schema.dmp LOGFILE=impdp_schema.log SCHEMAS=<schema>

impdp \"/ as sysdba\" DIRECTORY=imp_schema DUMPFILE=expdp_schema.dmp LOGFILE=impdp_schema.log SCHEMAS=<schema>

Doing schema imports to a pdb using impdp \"/ as sysdba\" method:

impdp \"sys/<sys-password>@<pdb-name> as sysdba\" DIRECTORY=imp_schema DUMPFILE=expdp_schema.dmp LOGFILE=impdp_schema.log SCHEMAS=<schema>

Import Schema to a different Schema:

impdp <username>/<password>@<database> DIRECTORY=imp_schema DUMPFILE=expdp_schema.dmp REMAP_SCHEMA=<source_schema>:<target_schema>

Specific Table Import:

impdp <username>/<password>@<database> DIRECTORY=imp_table DUMPFILE=expdp_table.dmp LOGFILE=impdp_table.log TABLES=<schema>.<table_name>

impdp \"/ as sysdba\" DIRECTORY=imp_table DUMPFILE=expdp_table.dmp LOGFILE=impdp_table.log TABLES=<schema>.<table_name>

Doing table imports to a pdb using impdp \"/ as sysdba\" method:

impdp \"sys/<sys-password>@<pdb-name> as sysdba\" DIRECTORY=imp_table DUMPFILE=expdp_table.dmp LOGFILE=impdp_table.log TABLES=<schema>.<table_name>

Import Table to a different Schema:

impdp \"sys/<sys-password>@<pdb-name> as sysdba\" DIRECTORY=imp_table DUMPFILE=expdp_table.dmp LOGFILE=impdp_table.log TABLES=<old_schema>.<table_name> REMAP_SCHEMA=<old_schema>:<new_schema>

Import Table to a different Table name (Requires Oracle 11g R2 or later):

impdp <username>/<password>@<database> DIRECTORY=imp_table DUMPFILE=expdp_table.dmp LOGFILE=impdp_table.log TABLES=<schema>.<old_table> REMAP_TABLE=<schema>.<old_table>:<new_table>

Import Table to a new Schema and new Table name:

impdp <username>/<password>@<database> DIRECTORY=imp_table DUMPFILE=expdp_table.dmp LOGFILE=impdp_table.log TABLES=<old_schema>.<old_table> REMAP_SCHEMA=<old_schema>:<new_schema> REMAP_TABLE=<old_schema>.<old_table>:<new_table>

Additional expdp parameters:

1. COMPRESSION=[ALL, DATA_ONLY, METADATA_ONLY, NONE]

ALL: Compresses both metadata and table data (Requires the Oracle Advanced Compression license)

DATA_ONLY: Compresses only the table data (Requires the Oracle Advanced Compression license)

METADATA_ONLY: Compresses only the metadata. This is the default behavior if the parameter is omitted.

NONE: Disables compression entirely resulting in the largest dumpfile.

2. COMPRESSION_ALGORITHM=[BASIC, LOW, MEDIUM, HIGH]

BASIC: Good compression. Moderate CPU usage (Default)

LOW: Fastest execution, least CPU impact but lowest compression ratio.

MEDIUM: Balanced between compression size and CPU usage

HIGH: Maximum compression ratio but highest CPU overhead.

3. ENCRYPTION_PASSWORD=[<password>]

Providing an encryption password encrypts everything by default. But it is possible to define the scope using the ENCRYPTION parameter.

4. ENCRYPTION=[ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY]

ALL: Encrypts both metadata and data (Default if password is provided).

DATA_ONLY: Encrypts only the table data.

ENCRYPTED_COLUMNS_ONLY: Encrypts only columns already encrypted by Transparent Data Encryption (TDE) in the database.

METADATA_ONLY: Encrypts only the metadata.

You can also define the encryption algorithm using the ENCRYPTION_ALGORITHM parameter.

ENCRYPTION_ALGORITHM=[AES128 (Default), AES192, AES256]

5. ESTIMATE=[BLOCKS, STATISTICS]

BLOCKS: Calculates the estimate by multiplying the number of database blocks used by the source objects by the database block size.

Pros: Extremely fast.

Cons: Can overestimate the size if your tables are heavily fragmented (e.g., if many rows have been deleted but the space hasn't been reclaimed).

STATISTICS: Calculates the estimate using the optimizer statistics gathered on the tables.

Pros: Highly accurate if your database statistics are fresh.

Cons: Can be wildly inaccurate if your statistics are stale or missing.

6. ESTIMATE_ONLY=[YES]

If you want to know how big the dump file will be without actually running the export, use the ESTIMATE_ONLY parameter.

When set to YES, Data Pump calculates the size, prints the estimate to the log/screen and then cleanly exits without creating a dump file or exporting any data.

(Note: When using ESTIMATE_ONLY you do not need to provide a DUMPFILE parameter)

7. EXCLUDE=SCHEMA:"='HR'"

Standard Syntax: EXCLUDE=object_type[:"name_clause"]

Exclude an Entire Object Type: The most common use case is excluding statistics to speed up imports or because you plan to gather fresh statistics later.

expdp <user>/<pass> DIRECTORY=exp_dir DUMPFILE=export.dmp SCHEMAS=hr EXCLUDE=STATISTICS

(Other common types to exclude: INDEX, GRANT, CONSTRAINT, TRIGGER)

Exclude Specific Tables by Name: You can use standard SQL operators like =, IN, or LIKE inside the name clause.

# Exclude a single table

EXCLUDE=TABLE:"='AUDIT_LOG'"

# Exclude multiple tables

EXCLUDE=TABLE:"IN ('TEMP_DATA', 'OLD_BACKUP')"

# Exclude tables matching a pattern

EXCLUDE=TABLE:"LIKE 'STG_%'"

When running Data Pump from a Unix/Linux shell, special characters like quotes " and parentheses () are often interpreted by the shell, causing syntax errors. The best practice is to use a parameter file (.par) to avoid this.

8. PARFILE=[<parameter_file>.par]

The PARFILE (Parameter File) parameter is one of the most useful features in Oracle Data Pump. Instead of typing a massive, complex command into your terminal, you store all your parameters in a simple text file and tell Data Pump to read from it.

expdp <username>/<password>@<database> PARFILE=export.par

9. PARALLEL=[<number_of_processes>]

For PARALLEL to actually work, you must have multiple dump files. Multiple processes cannot write to a single file at the same time. You must use the %U wildcard in your DUMPFILE parameter, which tells Oracle to automatically generate file names.

expdp <user>/<pass> DIRECTORY=exp_dir DUMPFILE=hr_data_%U.dmp PARALLEL=4

CPU Limits: A general best practice is to set PARALLEL to no more than 2 times the number of CPU cores available on the database server. Setting it too high will cause CPU thrashing.

Additional impdp parameters:

1. ENCRYPTION_PASSWORD=[<password>]

If the export files are encrypted, you have to provide the password used for the encryption in order to import the data.

2. EXCLUDE=SCHEMA:"='HR'"

Standard Syntax: EXCLUDE=object_type[:"name_clause"]

Exclude an Entire Object Type: The most common use case is excluding statistics to speed up imports or because you plan to gather fresh statistics later.

impdp <user>/<pass> DIRECTORY=imp_dir DUMPFILE=export.dmp SCHEMAS=hr EXCLUDE=STATISTICS

Exclude Specific Tables by Name: You can use standard SQL operators like =, IN, or LIKE inside the name clause.

# Exclude a single table

EXCLUDE=TABLE:"='AUDIT_LOG'"

# Exclude multiple tables

EXCLUDE=TABLE:"IN ('TEMP_DATA', 'OLD_BACKUP')"

# Exclude tables matching a pattern

EXCLUDE=TABLE:"LIKE 'STG_%'"

When running Data Pump from a Unix/Linux shell, special characters like quotes " and parentheses () are often interpreted by the shell, causing syntax errors. The best practice is to use a parameter file (.par) to avoid this.

3. PARFILE=[<parameter_file>.par]

The PARFILE (Parameter File) parameter is one of the most useful features in Oracle Data Pump. Instead of typing a massive, complex command into your terminal, you store all your parameters in a simple text file and tell Data Pump to read from it.

impdp <username>/<password>@<database> PARFILE=export.par

4. PARALLEL=[<number_of_processes>]

When importing with PARALLEL, you use the exact same %U wildcard so Oracle knows to read from all the generated files concurrently:

impdp <user>/<pass> DIRECTORY=imp_dir DUMPFILE=hr_data_%U.dmp PARALLEL=4

CPU Limits: A general best practice is to set PARALLEL to no more than 2 times the number of CPU cores available on the database server. Setting it too high will cause CPU thrashing.

PARFILE use case

Parfile content:

DIRECTORY=exp_dir

DUMPFILE=hr_data_%U.dmp

LOGFILE=hr_export.log

SCHEMAS=hr

EXCLUDE=STATISTICS

EXCLUDE=TABLE:"LIKE 'TEMP_%'"

PARALLEL=4

COMPRESSION=ALL

Execution:

expdp <username>/<password>@<database> PARFILE=export_hr.par