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

• Use PARALLEL to speed up large exports.
• Combine COMPRESSION and ENCRYPTION to secure and optimize dump files.
• Review log files for warnings or errors after the export.
• Test dump files’ integrity before importing into a new database.
• Regularly update directory objects and ensure permissions are set correctly.

Conclusion

Oracle Data Pump Export (EXPDP) is an indispensable tool for database administrators. It provides flexibility and power for efficient data management. Mastering EXPDP will simplify your DBA tasks and enhance database reliability.

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