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.<\/p>\n\n\n\n
nohup impdp '\"\/ as sysdba\"' DIRECTORY=directory_name \n DUMPFILE=dumpfile_name.dmp [,dumpfile_name2.dmp, dumpfile_name3.dmp, ...] \n LOGFILE=logfile_name.log \n FULL=Y|N \n SCHEMAS=schema_name [,schema_name] \n TABLES=table_name [,table_name] or owner1.tablename, owner2.tablename\n INCLUDE=object_type [,object_type] \n EXCLUDE=object_type [,object_type] \n CONTENT=ALL|DATA_ONLY|METADATA_ONLY \n FLASHBACK_SCN=scn_number \n FLASHBACK_TIME=\"timestamp\" \n NETWORK_LINK=network_link_name \n REMAP_SCHEMA=old_schema:new_schema \n REMAP_TABLESPACE=old_tablespace:new_tablespace \n TRANSPORTABLE=ALWAYS|AUTOMATIC|NEVER \n COMPRESSION=ALL|DATA|METADATA|NONE \n ESTIMATE_ONLY=Y|N \n PARALLEL=number_of_threads \n QUERY=table_name:\"WHERE condition\" \n REUSE_DATAFILES=Y|N \n ROWS=Y|N \n TABLESPACES=tablespace_name [,tablespace_name] \n VERSION=version_number \n JOB_NAME=job_name \n ENCRYPTION=ALL|DATA|METADATA|NONE \n ENCRYPTION_ALGORITHM=algorithm_name \n ENCRYPTION_PASSWORD=password \n ENCRYPTION_TRANSFORM=transform_name \n LOGTIME=ALL|NONE|DATE \n DATA_OPTIONS=option [,option] \n TRANSFORM=transform_option [,transform_option] \n DISABLE_ARCHIVE_LOGGING=Y|N \n RAC=Y|N &<\/code><\/pre>\n\n\n\nExample IMPDP Command<\/h2>\n\n\n\n
Below is a practical example demonstrating the use of multiple parameters in an IMPDP command:<\/p>\n\n\n\n
nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir \n DUMPFILE=my_dumpfile_%U.dmp,my_dumpfile_%U_%I.dmp \n LOGFILE=my_import.log \n FULL=N \n SCHEMAS=hr,sales \n TABLES=employees,departments or owner1.tablename, owner2.tablename \n INCLUDE=TABLE:\"IN ('EMPLOYEES', 'DEPARTMENTS')\" \n EXCLUDE=TABLE:\"IN ('TEMP_TABLES')\" \n CONTENT=ALL \n FLASHBACK_SCN=123456789 \n FLASHBACK_TIME=\"2024-08-20 12:00:00\" \n NETWORK_LINK=my_remote_db_link \n REMAP_SCHEMA=old_hr:new_hr \n REMAP_TABLESPACE=old_tablespace:new_tablespace \n TRANSPORTABLE=AUTOMATIC \n COMPRESSION=ALL \n ESTIMATE_ONLY=N \n PARALLEL=4 \n QUERY=employees:\"WHERE department_id=10\" \n REUSE_DATAFILES=N \n ROWS=Y \n TABLESPACES=users,system \n VERSION=19 \n JOB_NAME=my_import_job \n ENCRYPTION=DATA \n ENCRYPTION_ALGORITHM=AES256 \n ENCRYPTION_PASSWORD=my_password \n ENCRYPTION_TRANSFORM=PASSWORD \n LOGTIME=DATE \n DATA_OPTIONS=ROWS \n TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y,CONSTRAINTS:N\n DISABLE_ARCHIVE_LOGGING=Y|N\n RAC=Y &<\/code><\/pre>\n\n\n\nParameters and Descriptions<\/h2>\n\n\n\nEssential Parameters<\/h3>\n\n\n\n\n- DIRECTORY<\/strong>: Directory object where dump files are located.
Example: DIRECTORY=my_dump_dir<\/code><\/li>\n\n\n\n- DUMPFILE<\/strong>: Specifies the dump file(s) to be imported.\n
\n- Single file:
dumpfile_name.dmp<\/code><\/li>\n\n\n\n- Multiple files with suffixes:
dumpfile_name_%U.dmp<\/code> or dumpfile_name_%U_%I.dmp<\/code>
Example: DUMPFILE=my_dumpfile_%U.dmp,my_dumpfile_%U_%I.dmp<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n- LOGFILE<\/strong>: Name of the log file to record import details.
Example: LOGFILE=my_import.log<\/code><\/li>\n\n\n\n- FULL<\/strong>: Indicates whether to import the entire database (
Y<\/code>) or a subset (N<\/code>).
Example: FULL=N<\/code><\/li>\n\n\n\n- SCHEMAS<\/strong>: List of schemas to import.
Example: SCHEMAS=hr,sales<\/code><\/li>\n\n\n\n- TABLES<\/strong>: List of tables to import.
Example: TABLES=employees,departments or owner1.table1,owner2.table2<\/code><\/li>\n<\/ul>\n\n\n\nData Filtering<\/h3>\n\n\n\n\n- INCLUDE<\/strong>: Object types to include in the import.
Example: INCLUDE=TABLE:\"IN ('EMPLOYEES', 'DEPARTMENTS')\"<\/code><\/li>\n\n\n\n- EXCLUDE<\/strong>: Object types to exclude from the import.
Example: EXCLUDE=TABLE:\"IN ('TEMP_TABLES')\"<\/code><\/li>\n\n\n\n- QUERY<\/strong>: SQL condition to filter data during import.
Example: QUERY=employees:\"WHERE department_id=10\"<\/code><\/li>\n<\/ul>\n\n\n\nAdvanced Features<\/h3>\n\n\n\n\n- REMAP_SCHEMA<\/strong>: Remaps objects from one schema to another.
Example: REMAP_SCHEMA=old_hr:new_hr<\/code><\/li>\n\n\n\n- REMAP_TABLESPACE<\/strong>: Remaps objects to a different tablespace.
Example: REMAP_TABLESPACE=old_tablespace:new_tablespace<\/code><\/li>\n\n\n\n- TRANSPORTABLE<\/strong>: Enables transportable tablespaces mode (
ALWAYS<\/code>, AUTOMATIC<\/code>, NEVER<\/code>).
Example: TRANSPORTABLE=AUTOMATIC<\/code><\/li>\n\n\n\n- COMPRESSION<\/strong>: Compression type for the data being imported.
Example: COMPRESSION=ALL<\/code><\/li>\n<\/ul>\n\n\n\nPerformance and Parallelism<\/h3>\n\n\n\n\n- PARALLEL<\/strong>: Number of threads for parallel import.
Example: PARALLEL=4<\/code><\/li>\n\n\n\n- ROWS<\/strong>: Imports data rows (
Y<\/code>) or metadata only (N<\/code>).
Example: ROWS=Y<\/code><\/li>\n\n\n\n- DISABLE_ARCHIVE_LOGGING<\/strong>: Suppresses redo generation during import.
Example: DISABLE_ARCHIVE_LOGGING=Y<\/code><\/li>\n\n\n\n- ESTIMATE_ONLY<\/strong>: Estimates the size without performing the import (
Y<\/code> or N<\/code>).
Example: ESTIMATE_ONLY=N<\/code><\/li>\n<\/ul>\n\n\n\nSecurity Features<\/h3>\n\n\n\n\n- ENCRYPTION<\/strong>: Specifies the encryption type for the import.
Example: ENCRYPTION=DATA<\/code><\/li>\n\n\n\n- ENCRYPTION_ALGORITHM<\/strong>: Defines the algorithm for encryption.
Example: ENCRYPTION_ALGORITHM=AES256<\/code><\/li>\n\n\n\n- ENCRYPTION_PASSWORD<\/strong>: Password used for encryption.
Example: ENCRYPTION_PASSWORD=my_password<\/code><\/li>\n<\/ul>\n\n\n\nLogging and Debugging<\/h3>\n\n\n\n\n- LOGTIME<\/strong>: Adds timestamps to log entries (
ALL<\/code>, DATE<\/code>, NONE<\/code>).
Example: LOGTIME=DATE<\/code><\/li>\n\n\n\n- DATA_OPTIONS<\/strong>: Additional options for handling data.
Example: DATA_OPTIONS=ROWS<\/code><\/li>\n\n\n\n- JOB_NAME<\/strong>: Specifies a custom name for the import job.
Example: JOB_NAME=my_import_job<\/code><\/li>\n<\/ul>\n\n\n\nOracle 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-91","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/91","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=91"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/91\/revisions"}],"predecessor-version":[{"id":93,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/91\/revisions\/93"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=91"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=91"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=91"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}