Oracle Data Pump Export (EXPDP): A Complete Guide

Oracle EXPDP is part of the Oracle Data Pump suite, allowing you to export database objects, data, and metadata into dump files. These dump files can then be imported into another database using Oracle Data Pump Import (IMPDP).

Key Features of EXPDP

• Parallel Processing: Speeds up large exports by using multiple threads.
• Compression: Reduces dump file sizes by compressing data and/or metadata.
• Fine-Grained Control: Allows selective export using parameters like SCHEMAS, TABLES, QUERY, and more.
• Transportable Tablespaces: Facilitates fast migration of large datasets between databases.
• Flashback Technology: Ensures consistency of the exported data by using Oracle’s flashback features.

EXPDP Command Syntax

The basic syntax for running an EXPDP command is:

nohup expdp '"/ as sysdba"' DIRECTORY=directory_name 
  DUMPFILE=dumpfile_name.dmp [,dumpfile_name2.dmp, dumpfile_name3.dmp, ...] 
  LOGFILE=logfile_name.log 
  FULL=Y|N 
  SCHEMAS=schema_name [,schema_name] 
  TABLES=table_name [,table_name] or owner.tablename
  INCLUDE=object_type [,object_type] 
  EXCLUDE=object_type [,object_type] 
  CONTENT=ALL|DATA_ONLY|METADATA_ONLY 
  FLASHBACK_SCN=scn_number 
  FLASHBACK_TIME="timestamp" 
  NETWORK_LINK=network_link_name 
  TRANSPORT_TABLESPACES=tablespace_name [,tablespace_name] 
  TRANSPORTABLE=ALWAYS|AUTOMATIC|NEVER 
  COMPRESSION=ALL|DATA|METADATA|NONE 
  ESTIMATE_ONLY=Y|N 
  PARALLEL=number_of_threads 
  QUERY=table_name:"WHERE condition" 
  REUSE_DUMPFILES=Y|N 
  ROWS=Y|N 
  TABLESPACES=tablespace_name [,tablespace_name] 
  VERSION=version_number 
  JOB_NAME=job_name 
  ENCRYPTION=ALL|DATA|METADATA|NONE 
  ENCRYPTION_ALGORITHM=algorithm_name 
  ENCRYPTION_PASSWORD=password 
  ENCRYPTION_TRANSFORM=transform_name 
  LOGTIME=ALL|NONE|DATE 
  DATA_OPTIONS=option [,option] 
  TRANSFORM=transform_option [,transform_option] 
  DISABLE_ARCHIVE_LOGGING=Y|N
  RAC=Y|N &

Example: EXPDP Command

Here’s a practical example to demonstrate the usage of multiple parameters:

nohup expdp '"/ as sysdba"' DIRECTORY=my_dump_dir 
  DUMPFILE=my_dumpfile_%U.dmp,my_dumpfile_%U_%I.dmp 
  LOGFILE=my_export.log 
  FULL=N 
  SCHEMAS=hr,sales 
  TABLES=employees,departments or owner1.tablename, owner2.tablename
  INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')" 
  EXCLUDE=TABLE:"IN ('TEMP_TABLES')" 
  CONTENT=ALL 
  FLASHBACK_SCN=123456789 
  FLASHBACK_TIME="2024-08-20 12:00:00" 
  NETWORK_LINK=my_remote_db_link 
  TRANSPORT_TABLESPACES=users,system 
  TRANSPORTABLE=AUTOMATIC 
  COMPRESSION=ALL 
  ESTIMATE_ONLY=N 
  PARALLEL=4 
  QUERY=employees:"WHERE department_id=10" 
  REUSE_DUMPFILES=N 
  ROWS=Y 
  TABLESPACES=users,system 
  VERSION=19 
  JOB_NAME=my_export_job 
  ENCRYPTION=DATA 
  ENCRYPTION_ALGORITHM=AES256 
  ENCRYPTION_PASSWORD=my_password 
  ENCRYPTION_TRANSFORM=PASSWORD 
  LOGTIME=DATE 
  DATA_OPTIONS=ROWS 
  TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y,CONSTRAINTS:N
  DISABLE_ARCHIVE_LOGGING=Y
  RAC=Y &

EXPDP Parameters Explained

1. DIRECTORY

Specifies the directory object where dump files and log files are written.

• Example: DIRECTORY=my_dump_dir

2. DUMPFILE

Defines the names of the dump files to be created. Supports wildcard suffixes for multiple files.

• Example: DUMPFILE=my_dumpfile_%U.dmp

3. LOGFILE

Specifies the log file to record the export operation.

• Example: LOGFILE=my_export.log

4. FULL

Exports the entire database when set to Y. Default is N.

• Example: FULL=Y

5. SCHEMAS

Exports specified schemas.

• Example: SCHEMAS=hr,sales

6. TABLES

Exports specific tables.

• Example: TABLES=employees,departments

7. INCLUDE/EXCLUDE

Includes or excludes specific object types during the export.

• Example: INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

8. CONTENT

Specifies whether to export all data, only data, or only metadata.

• Options: ALL, DATA_ONLY, METADATA_ONLY
• Example: CONTENT=ALL

9. FLASHBACK_SCN and FLASHBACK_TIME

Ensures data consistency by using Oracle’s Flashback technology.

• SCN Example: FLASHBACK_SCN=123456789
• Timestamp Example: FLASHBACK_TIME="2024-08-20 12:00:00"

10. PARALLEL

Specifies the number of threads for parallel processing.

• Example: PARALLEL=4

11. COMPRESSION

Compresses data, metadata, or both to reduce dump file size.

• Options: ALL, DATA, METADATA, NONE
• Example: COMPRESSION=ALL

12. ENCRYPTION and Related Parameters

Encrypts the exported dump file.

• Algorithm Example: ENCRYPTION_ALGORITHM=AES256
• Password Example: ENCRYPTION_PASSWORD=my_password

13. QUERY

Filters data to be exported using a SQL WHERE clause.

• Example: QUERY=employees:"WHERE department_id=10"

14. TRANSPORT_TABLESPACES

Enables the export of transportable tablespaces.

• Example: TRANSPORT_TABLESPACES=users,system

15. TRANSFORM

Transforms data or metadata during the export.

• Example: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Best Practices for Using EXPDP

1. Use PARALLEL to speed up large exports.
2. Combine COMPRESSION and ENCRYPTION to secure and optimize dump files.
3. Always review the log file for warnings or errors post-export.
4. Test the integrity of dump files before importing them into a new database.
5. Regularly update directory objects and ensure permissions are set correctly.

Oracle Data Pump Export is an indispensable tool for database administrators, offering flexibility and power to manage data efficiently. Mastering EXPDP will not only simplify your DBA tasks but also enhance the reliability of your database operations.

Stay tuned for our guide on Oracle Data Pump Import (IMPDP) to complement your learning!