Oracle Data Pump Import (IMPDP): A Complete Guide

Oracle Data Pump Import (IMPDP) is a robust utility provided by Oracle for importing data and metadata into an Oracle database. Designed to be more efficient and flexible than traditional import utilities, IMPDP supports advanced features such as parallel processing, data transformations, and remapping. It is commonly used to restore data from logical backups or migrate data between databases.

IMPDP Command Syntax

nohup impdp '"/ 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 owner1.tablename, owner2.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 
  REMAP_SCHEMA=old_schema:new_schema 
  REMAP_TABLESPACE=old_tablespace:new_tablespace 
  TRANSPORTABLE=ALWAYS|AUTOMATIC|NEVER 
  COMPRESSION=ALL|DATA|METADATA|NONE 
  ESTIMATE_ONLY=Y|N 
  PARALLEL=number_of_threads 
  QUERY=table_name:"WHERE condition" 
  REUSE_DATAFILES=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 IMPDP Command

Below is a practical example demonstrating the use of multiple parameters in an IMPDP command:

nohup impdp '"/ as sysdba"' DIRECTORY=my_dump_dir 
  DUMPFILE=my_dumpfile_%U.dmp,my_dumpfile_%U_%I.dmp 
  LOGFILE=my_import.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 
  REMAP_SCHEMA=old_hr:new_hr 
  REMAP_TABLESPACE=old_tablespace:new_tablespace 
  TRANSPORTABLE=AUTOMATIC 
  COMPRESSION=ALL 
  ESTIMATE_ONLY=N 
  PARALLEL=4 
  QUERY=employees:"WHERE department_id=10" 
  REUSE_DATAFILES=N 
  ROWS=Y 
  TABLESPACES=users,system 
  VERSION=19 
  JOB_NAME=my_import_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|N
  RAC=Y &

Parameters and Descriptions

Essential Parameters

• DIRECTORY: Directory object where dump files are located.
  Example: DIRECTORY=my_dump_dir
• DUMPFILE: Specifies the dump file(s) to be imported.
  • Single file: dumpfile_name.dmp
  • Multiple files with suffixes: dumpfile_name_%U.dmp or dumpfile_name_%U_%I.dmp
    Example: DUMPFILE=my_dumpfile_%U.dmp,my_dumpfile_%U_%I.dmp
• LOGFILE: Name of the log file to record import details.
  Example: LOGFILE=my_import.log
• FULL: Indicates whether to import the entire database (Y) or a subset (N).
  Example: FULL=N
• SCHEMAS: List of schemas to import.
  Example: SCHEMAS=hr,sales
• TABLES: List of tables to import.
  Example: TABLES=employees,departments or owner1.table1,owner2.table2

Data Filtering

• INCLUDE: Object types to include in the import.
  Example: INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"
• EXCLUDE: Object types to exclude from the import.
  Example: EXCLUDE=TABLE:"IN ('TEMP_TABLES')"
• QUERY: SQL condition to filter data during import.
  Example: QUERY=employees:"WHERE department_id=10"

Advanced Features

• REMAP_SCHEMA: Remaps objects from one schema to another.
  Example: REMAP_SCHEMA=old_hr:new_hr
• REMAP_TABLESPACE: Remaps objects to a different tablespace.
  Example: REMAP_TABLESPACE=old_tablespace:new_tablespace
• TRANSPORTABLE: Enables transportable tablespaces mode (ALWAYS, AUTOMATIC, NEVER).
  Example: TRANSPORTABLE=AUTOMATIC
• COMPRESSION: Compression type for the data being imported.
  Example: COMPRESSION=ALL

Performance and Parallelism

• PARALLEL: Number of threads for parallel import.
  Example: PARALLEL=4
• ROWS: Imports data rows (Y) or metadata only (N).
  Example: ROWS=Y
• DISABLE_ARCHIVE_LOGGING: Suppresses redo generation during import.
  Example: DISABLE_ARCHIVE_LOGGING=Y
• ESTIMATE_ONLY: Estimates the size without performing the import (Y or N).
  Example: ESTIMATE_ONLY=N

Security Features

• ENCRYPTION: Specifies the encryption type for the import.
  Example: ENCRYPTION=DATA
• ENCRYPTION_ALGORITHM: Defines the algorithm for encryption.
  Example: ENCRYPTION_ALGORITHM=AES256
• ENCRYPTION_PASSWORD: Password used for encryption.
  Example: ENCRYPTION_PASSWORD=my_password

Logging and Debugging

• LOGTIME: Adds timestamps to log entries (ALL, DATE, NONE).
  Example: LOGTIME=DATE
• DATA_OPTIONS: Additional options for handling data.
  Example: DATA_OPTIONS=ROWS
• JOB_NAME: Specifies a custom name for the import job.
  Example: JOB_NAME=my_import_job

Oracle Data Pump Import is a versatile and high-performance utility for managing imports in Oracle databases. By understanding the key parameters and their configurations, you can efficiently restore or migrate data to meet your requirements.