Data Pump enhancements

There are quite a few useful additions, such as converting view into a table while exporting and turning off logging while import.

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.

  • %d, %D : The current day of the month in DD format.
  • %m, %M : The current month in MM format.
  • %y, %Y : The current year in YYYY format.
  • %t, %T : The current date in YYYYMMDD format.

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

  • %U : Unchanged from previous releases. A two digit number incremented between 01-99.
  • %l, %L : This starts off similar to “%U”, producing a two digit number between 01-99, but it can extend up to 2147483646, so the resulting file name is not a fixed length.

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@DB schemas=scott directory=TEST dumpfile=scott_%T_%L.dmp logfile=expdpscott.log parallel=4
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
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 Information 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

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 - 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 - 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


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.


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

cat > /tmp/parfile.txt <<EOF

impdp parfile=/tmp/parfile.txt

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


Turn off redo log generation
The new TRANSFORM option introduced in data pumps import provides the flexibility to turn off the redo generation for the objects during the course of import. When DISABLE_ARCHIVE_LOGGING values is specified with the TRANSFORM option, redo generation for the objects in the context will be turned off during the entire import duration. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
This example demonstrates this feature:
$ ./impdp directory=dpump dumpfile=abcd.dmp logfile=abcd.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Transport view as table
This is another improvement in the data pumps. With the new VIEWS_AS_TABLES option, you can unload the view data into a table. The following example describes how to unload views data into a table during export:
$ ./expdp directory=dpump dumpfile=abcd.dmp logfile=abcd.log views_as_tables=my_view:my_table