Category Archives: DataPump

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

Continue reading Data Pump enhancements