8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Data Pump (expdp, impdp) Enhancements in Oracle Database 11g Release 1

Oracle Data Pump was introduced in Oracle 10g. This article provides an overview of the main Data Pump enhancements in Oracle Database 11g Release 1, including the following.

Related articles.

COMPRESSION

The COMPRESSION parameter allows you to decide what, if anything, you wish to compress in your export. The syntax is shown below.

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

The available options are:

Here is an example of the COMPRESSION parameter being used.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  compression=all
The COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option, which is available with a COMPATIBLE setting of "10.2".

Data compression requires the Advanced Compression Option option of Enterprise Edition, as described here.

Encryption Parameters

Data pump encryption is an Enterprise Edition feature, so the parameters described below are only relevant for Enterprise Edition installations. In addition, the COMPATIBLE initialisation parameter must be set to "11.0.0" or higher to use these features.

ENCRYPTION and ENCRYPTION_PASSWORD

The use of encryption is controlled by a combination of the ENCRYPTION or ENCRYPTION_PASSWORD parameters. The syntax for the ENCRYPTION parameter is shown below.

ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}

The available options are:

If neither the ENCRYPTION or ENCRYPTION_PASSWORD parameters are set, it is assumed the required level of encryption is NONE. If only the ENCRYPTION_PASSWORD parameter is specified, it is assumed the required level of encryption is ALL. Here is an example of these parameters being used.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  encryption=all encryption_password=password

ENCRYPTION_ALGORITHM

The ENCRYPTION_ALGORITHM parameter specifies the encryption algorithm to be used during the export, with the default being "AES128". The syntax is shown below.

ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

The ENCRYPTION_ALGORITHM parameter must be used in conjunction with the ENCRYPTION or ENCRYPTION_PASSWORD parameters, as shown below.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  encryption=all encryption_password=password encryption_algorithm=AES256

ENCRYPTION_MODE

The ENCRYPTION_MODE parameter specifies the type of security used during export and import operations. The syntax is shown below.

ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

The allowable values and their default settings are explained below:

Wallet setup is described here.

The ENCRYPTION_MODE requires either the ENCRYPTION or ENCRYPTION_PASSWORD parameter to be specified.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  encryption=all encryption_password=password encryption_mode=password

TRANSPORTABLE

The TRANSPORTABLE parameter is similar to the TRANSPORT_TABLESPACES parameter available previously in that it only exports and imports metadata about a table, relying on you to manually transfer the relevent tablespace datafiles. The export operation lists the tablespaces that must be transfered. The syntax is shown below.

TRANSPORTABLE = {ALWAYS | NEVER}

The value ALWAYS turns on the transportable mode, while the default value of NEVER indicates this is a regular export/import.

The following restrictions apply during exports using the TRANSPORTABLE parameter:

Some extra restictions apply during import operations:

Examples of the export and import operations are shown below.

expdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  transportable=ALWAYS

impdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
  transportable=ALWAYS network_link=DB11G transport_datafiles='/u01/oradata/DB11G/test01.dbf'

PARTITION_OPTIONS

The PARTITION_OPTIONS parameter determines how partitions will be handled during export and import operations. The syntax is shown below.

PARTITION_OPTIONS={none | departition | merge}

The allowable values are:

The NONE and MERGE options are not possible if the export was done using the TRANSPORTABLE parameter with a partition or subpartition filter. If there are any grants on objects being departitioned, an error message is generated and the objects are not loaded.

expdp test/test directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log tables=test.tab1
  partition_options=merge

REUSE_DUMPFILES

The REUSE_DUMPFILES parameter can be used to prevent errors being issued if the export attempts to write to a dump file that already exists.

REUSE_DUMPFILES={Y | N}

When set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  reuse_dumpfiles=y

REMAP_TABLE

This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE method. It can also be used to alter the base table name used during PARTITION_OPTIONS imports. The syntax is shown below.

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

An example is shown below.

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
  remap_table=TEST.TAB1:TAB2

Existing tables are not renamed, only tables created by the import.

DATA_OPTIONS

SKIP_CONSTRAINT_ERRORS

During import operations using the external table acces method, setting the DATA_OPTIONS parameter to SKIP_CONSTRAINT_ERRORS allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without this, the default action would be to roll back the whole operation. The syntax is shown below.

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

An example is shown below.

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
  data_options=SKIP_CONSTRAINT_ERRORS

This parameter has no impact on deferred constraints, which still cause the operation to be rolled back once a violation is detected. If the object being loaded has existing unique indexes or constraints, the APPEND hint will not be used, which may adversely affect performance.

XML_CLOBS

During an export, if XMLTYPE columns are currently stored as CLOBs, they will automatically be exported as uncompressed CLOBs. If on the other hand they are currently stored as any combination of object-relational, binary or CLOB formats, they will be exported in compressed format by default. Setting the DATA_OPTIONS parameter to XML_CLOBS specifies that all XMLTYPE columns should be exported as uncompressed CLOBs, regardless of the default action. The syntax is shown below.

DATA_OPTIONS=XML_CLOBS

An example is shown below.

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  version=11.1 data_options=XML_CLOBS

Both the export and import must use the same XML schema and the job version must be set to 11.0.0 or higher.

REMAP_DATA

During export and import operations, the REMAP_DATA parameter allows you to associate a remap packaged function that will accept the column value as a parameter and return a modified version of the data. The syntax is shown below.

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

This can be used to mask sensitive data during export and import operations by replacing the original data with random alternatives. The mapping is done on a column-by-column basis, as shown below.

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  remap_data:tab1.col1:remap_pkg.remap_col1
  remap_data:tab1.col2:remap_pkg.remap_col2

The remapping function must return the same datatype as the source column and it must not perform a commit or rollback.

Miscellaneous Enhancements

Worker processes that have stopped due to certain errors will now have a one-time automatic restart. If the process stops a second time, it must be restarted manually.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.