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

Home » Articles » 12c » Here

Data Pump (expdp, impdp) Enhancements in Oracle Database 12c Release 2 (12.2)

This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 2 (12.2).

Some of these features are not listed in the expdp -help or impdp -help usage text.

Related articles.

Parallel Export/Import of Metadata

In previous releases the PARALLEL parameter was only relevant to the export/import of data, with all metadata processed serially. In Oracle 12.2 both metadata and data can be exported in parallel provided it is not a transportable tablespace operation.

The order of operations is important during an import, so the possible levels of parallelism may vary throughout the import job. If possible, the import will use parallelism up to the value of the PARALLEL parameter for both metadata and data import.

Wildcards in TRANSPORT_DATAFILES

When using the TRANSPORT_DATAFILES parameter, the datafile definition can now use wildcards in the file name.

The wildcards are not allowed in directory names, just file names, and the wildcards can't match files that are not present in the transport set, or an error will be produced.

# This
transport_datafiles=/my/path/file10.dbf,/my/path/file11.dbf,/my/path/file12.dbf

# becomes one of these alternatives.
transport_datafiles=/my/path/file*.dbf
transport_datafiles=/my/path/file1*.dbf
transport_datafiles=/my/path/file1?.dbf

New Substitution Variables for File Names

Multiple files are generated by parallel exports, so each file needs to have a unique name. This is achieved using substitution variables. In previous releases the only substitution variable available was "%U", which generated a two digit number from 01-99. Oracle 12.2 includes additional substitution variables.

The following substitution variables are only available for export operations.

The following substitution variables are available for both export and import operations.

The following example shows the usage of two of the new substitution variables in a parallel export. The output is edited to reduce its size.

$ expdp system/OraPasswd1@pdb1 schemas=OE directory=TEST_DIR dumpfile=OE_%T_%L.dmp logfile=expdpOE.log parallel=4

Export: Release 12.2.0.1.0 - Production on Wed Mar 22 16:04:32 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@pdb1 schemas=OE directory=TEST_DIR dumpfile=OE_%T_%L.dmp logfile=expdpOE.log parallel=4
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
.
.
.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/OE_20170322_01.dmp
  /tmp/OE_20170322_02.dmp
  /tmp/OE_20170322_03.dmp
  /tmp/OE_20170322_04.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Wed Mar 22 16:05:41 2017 elapsed 0 00:01:07

$

Parameter File Contents Written to Log File

The contents of the parameter file specified by the PARFILE parameter is written to the logfile, but not echoed to the screen.

Create the following parameter file and run an export using it.

cat > /tmp/parfile.txt <<EOF
USERID=test/test@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=expdpTEST.log
EOF

expdp parfile=/tmp/parfile.txt

If we check the top of the resulting log file we can see the parameter file contents.

$ head -15 /tmp/expdpTEST.log
;;;
Export: Release 12.2.0.1.0 - Production on Mon Aug 21 19:45:00 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
;;; **************************************************************************
;;; Parfile values:
;;;  parfile:  logfile=expdpTEST.log
;;;  parfile:  dumpfile=TEST.dmp
;;;  parfile:  directory=TEST_DIR
;;;  parfile:  schemas=TEST
;;;  parfile:  userid=test/********@pdb1
;;; **************************************************************************
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@pdb1 parfile=/tmp/parfile.txt
$

REMAP_DIRECTORY

The REMAP_DIRECTORY parameter is a variant of the REMAP_DATAFILE parameter, which allows you to remap the paths of multiple datafile references in a single shot. The REMAP_DIRECTORY and REMAP_DATAFILE parameters are mutually exclusive.

The basic syntax is a search and replace, with the documentation recommending the inclusion terminators and double-quotes. Since this would have to be escaped at the command line, it's easier to view the setting as it would be in a parameter file.

REMAP_DIRECTORY="'/u02/oradata/cdb1/pdb1/':'/u02/oradata/cdb3/pdb3/'"

You can see it in the context of a full parameter file below.

cat > /tmp/parfile.txt <<EOF
USERID=system/OraPasswd1@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=impdpTEST.log
remap_directory="'/u02/oradata/cdb1/pdb1/':'/u02/oradata/cdb3/pdb3/'"
EOF

impdp parfile=/tmp/parfile.txt

This parameter is not present in the impdp -help usage information.

DATA_OPTIONS Changes

There have been a number of changes to the DATA_OPTIONS parameter for export and import in Oracle Database 12.2. The new settings are displayed below in bold.

# Export
DATA_OPTIONS = [XML_CLOBS | GROUP_PARTITION_TABLE_DATA | VERIFY_STREAM_FORMAT]

# Import
DATA_OPTIONS = [DISABLE_APPEND_HINT | SKIP_CONSTRAINT_ERRORS | 
               ENABLE_NETWORK_COMPRESSION | REJECT_ROWS_WITH_REPL_CHAR | 
               TRUST_EXISTING_TABLE_PARTITIONS | VALIDATE_TABLE_DATA]

These settings are described in the manuals (export, import), but they are briefly described below.

You can see the new export DATA_OPTIONS settings in the context of a full parameter file below.

cat > /tmp/parfile.txt <<EOF
USERID=system/OraPasswd1@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=expdpTEST.log
data_options=group_partition_table_data,verify_stream_format
EOF

expdp parfile=/tmp/parfile.txt

You can see the new import DATA_OPTIONS settings in the context of a full parameter file below. The compression setting is ignored as this is not a network import.

cat > /tmp/parfile.txt <<EOF
USERID=system/OraPasswd1@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=impdpTEST.log
data_options=enable_network_compression,trust_existing_table_partitions,validate_table_data
EOF

impdp parfile=/tmp/parfile.txt

Miscellaneous

There are quite a few new features that don't need a demonstration or syntax example, including the following.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.