Oracle Database DataPump expdp impdp

Oracle Data Pump is a powerful utility for exporting and importing database objects and data. It was introduced in Oracle Database 10g as an enhancement to the older export/import utilities (exp and imp). Data Pump provides a more flexible, scalable, and high-performance mechanism for moving data between Oracle databases compared to its predecessor.

Here are some key features and concepts related to Oracle Data Pump:

Parallel Processing:

Data Pump enables parallelism during export and import operations, which significantly improves performance. Multiple worker processes can be employed to simultaneously process different database objects, leading to faster data movement.

Data Pump Export (expdp):

• The expdp command is used for exporting data and metadata from an Oracle database.
• It supports a wide range of export options, including exporting entire databases, specific schemas, tables, and subsets of data based on various criteria.
• The export process creates dump files that contain the exported data and a metadata description of the database objects.

Data Pump Import (impdp):

• The impdp command is used for importing data and metadata into an Oracle database.
• It supports options for remapping tablespaces, transforming data during the import, and more.
• The import process reads the dump files created during export and recreates the database objects in the target database.

Transportable Tablespaces:

• Data Pump allows for the transport of tablespaces between databases, providing a fast and efficient method for moving large volumes of data.
• This feature is particularly useful for scenarios where a subset of data needs to be moved between databases.

Data Pump Modes:

Full Mode: Exports or imports the entire database.
Schema Mode: Exports or imports one or more schemas.
Table Mode: Exports or imports specific tables or partitions.
Network Mode: Allows direct transfers between databases over a network.

Data Pump Parameters:

Oracle Data Pump provides a wide range of parameters that allow you to customize the behavior of export and import operations. These parameters can be specified on the command line when invoking expdp (export) or impdp (import). Here are some key Data Pump parameters and their purposes:

Common Parameters:

USERNAME/PASSWORD:

Specifies the Oracle username and password for connecting to the database.

Example: USERNAME=scott PASSWORD=tiger

DIRECTORY: Specifies the Oracle directory object where Data Pump files are to be read from (for import) or written to (for export).

Example: DIRECTORY=data_pump_dir

DUMPFILE/LOGFILE: Specifies the names of the dump file(s) and log file.

Example: DUMPFILE=exp_data.dmp, LOGFILE=exp_log.log

PARALLEL: Specifies the number of worker processes to use during the export or import operation, allowing for parallelism and improved performance.

Example: PARALLEL=4

Export-Specific Parameters:

SCHEMAS: Specifies a list of schemas to be exported.

Example: SCHEMAS=hr,sh

TABLES: Specifies a list of tables to be exported.

Example: TABLES=employees,departments

QUERY: Specifies a query clause to filter the data being exported.

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

EXCLUDE: Specifies a list of object types to be excluded from the export.

Example: EXCLUDE=INDEX,PACKAGE

Import-Specific Parameters:

REMAP_SCHEMA: Specifies a remapping of the source schema to a different target schema during import.

Example: REMAP_SCHEMA=source_schema:target_schema

REMAP_TABLESPACE: Specifies a remapping of the source tablespace to a different target tablespace during import.

Example: REMAP_TABLESPACE=source_tbs:target_tbs

TRANSFORM: Specifies transformations to be applied during import, such as data filtering or conversion.

Example: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

TABLE_EXISTS_ACTION: Specifies the action to be taken if a table being imported already exists in the target database.

Example: TABLE_EXISTS_ACTION=REPLACE

Additional Parameters:

NETWORK_LINK: Specifies the database link to be used for Network Mode. Enables direct transfer of data between databases over a network.

Example: NETWORK_LINK=source_link

ENCRYPTION: Specifies whether to encrypt data during the export or import operation.

Example: ENCRYPTION=ALL

COMPRESSION: Specifies whether to compress data during the export or import operation.

Example: COMPRESSION=ALL

JOB_NAME: Specifies a user-defined job name for the Data Pump job.

Example: JOB_NAME=my_export_job


Data Pump Views: Various Data Pump views provide information about ongoing and completed Data Pump jobs, making it easy to monitor and manage the export and import processes.

Security Considerations: Proper privileges are required for using Data Pump. The user must have the DBA role, the EXP_FULL_DATABASE role for exporting, and the IMP_FULL_DATABASE role for importing.