Google Cloud Technical Track

Database 12c: What's New with Data Pump? Lots.

16 min read

Aug 20, 2013

This article will discuss some of the new stuff on board with Oracle Database 12c and one of our favorite tools: data pump. When Oracle Data Pump hit the streets, there was a veritable gold mine of opportunities to play with the new toy. Seasoned presenters such as yours truly embraced the new product. It was a nice marriage for attendees at tech presentations on this topic. They were hungry for new stuff, and these sessions provided fast-tracked learning. Sounds to me like a dream come true for all. We will look at the following new parameters:

LOGGING
DISABLE_ARCHIVE_LOGGING (part of the TRANSFORM parameter)
ENCRYPTION_PWD_PROMPT
COMPRESSION_ALGORITHM

Let's get started...

LOGGING

DBAs and other technical personnel thirst for answers to nagging questions:

How long is this going to take?
Does the time-to-market to complete a job grow at the same rate as the data volume?
Can we predict how long work will take based on past experiences?

With Oracle Database 12c, some of these questions can be addressed by a new parameter introduced in Oracle Data Pump - LOGGING. This command-line parameter can have four values:

NONE: No timestamp information is displayed. (This is the default.)
STATUS: Timestamp messages on status are displayed.
LOGFILE: Same as STATUS, but only displayed for logfile messages.
ALL: A combination of STATUS and LOGFILE.

Recently, we needed to copy a schema from development to production for a client, and one of the approaches we considered was data pump exp/imp. While the jobs were running, we leveraged one of the DBA's best friends, V$SESSION_LONGOPS. Coupled with the information displayed based on the setting for the LOGGING command-line parameter, we have more information at our fingertips. Is this a big deal on its own? Some may say it is, and to those who don't: Remember that every enhancement rolled together with others becomes a big deal. Next is a quick look at a data pump export job with LOGGING set to ALL:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /home/oracle> expdp full=y dumpfile=pythian_logging logtime=all
 
 Export: Release 12.1.0.1.0 - Production on Fri Aug 16 18:11:24 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 Database Directory Object has defaulted to: "DPDUMP".
 16-AUG-13 18:11:35.115: Starting "SYS"."SYS_EXPORT_FULL_01": /******** AS SYSDBA full=y dumpfile=pythian_logging logtime=all 
 16-AUG-13 18:11:36.703: Estimate in progress using BLOCKS method...
 16-AUG-13 18:11:40.411: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 16-AUG-13 18:11:41.966: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
 16-AUG-13 18:11:43.494: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
 16-AUG-13 18:11:48.396: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
 16-AUG-13 18:11:48.594: Total estimation using BLOCKS method: 2.890 MB
 16-AUG-13 18:11:49.255: Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
 16-AUG-13 18:11:49.269: Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
 16-AUG-13 18:11:49.489: Processing object type DATABASE_EXPORT/TABLESPACE
 16-AUG-13 18:11:49.812: Processing object type DATABASE_EXPORT/PROFILE
 16-AUG-13 18:11:49.855: Processing object type DATABASE_EXPORT/SYS_USER/USER
 16-AUG-13 18:11:49.888: Processing object type DATABASE_EXPORT/SCHEMA/USER
 16-AUG-13 18:11:49.957: Processing object type DATABASE_EXPORT/ROLE
 16-AUG-13 18:11:49.991: Processing object type DATABASE_EXPORT/RADM_FPTM
 16-AUG-13 18:11:50.471: Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
 16-AUG-13 18:11:50.775: Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
 16-AUG-13 18:11:50.786: Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
 16-AUG-13 18:11:50.790: Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
 16-AUG-13 18:11:50.796: Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
 16-AUG-13 18:11:50.858: Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
 16-AUG-13 18:11:50.887: Processing object type DATABASE_EXPORT/RESOURCE_COST
 16-AUG-13 18:11:50.962: Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
 16-AUG-13 18:11:51.030: Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
 16-AUG-13 18:12:11.437: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
 16-AUG-13 18:12:20.503: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
 16-AUG-13 18:12:21.512: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
 16-AUG-13 18:12:23.077: Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
 16-AUG-13 18:12:43.930: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
 16-AUG-13 18:12:48.049: Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
 16-AUG-13 18:12:53.759: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
 16-AUG-13 18:13:26.664: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
 16-AUG-13 18:13:50.085: Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
 16-AUG-13 18:13:55.129: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
 16-AUG-13 18:14:08.783: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
 16-AUG-13 18:14:12.618: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
 16-AUG-13 18:14:12.682: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 16-AUG-13 18:14:13.987: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
 16-AUG-13 18:14:17.118: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
 16-AUG-13 18:14:17.141: Processing object type DATABASE_EXPORT/STATISTICS/MARKER
 16-AUG-13 18:14:32.021: Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
 16-AUG-13 18:14:33.141: Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
 16-AUG-13 18:14:38.490: Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
 16-AUG-13 18:14:38.911: Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
 16-AUG-13 18:14:38.977: Processing object type DATABASE_EXPORT/AUDIT
 16-AUG-13 18:14:39.201: Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
 16-AUG-13 18:14:41.683: . . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.054 KB 36 rows
 16-AUG-13 18:14:42.952: . . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows
 16-AUG-13 18:14:43.235: . . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.734 KB 8 rows
 16-AUG-13 18:14:43.382: . . exported "LBACSYS"."OLS$DIP_EVENTS" 5.515 KB 2 rows
 16-AUG-13 18:14:43.400: . . exported "LBACSYS"."OLS$INSTALLATIONS" 6.937 KB 2 rows
 16-AUG-13 18:14:43.444: . . exported "LBACSYS"."OLS$PROPS" 6.210 KB 5 rows
 16-AUG-13 18:14:43.487: . . exported "SYS"."DAM_CONFIG_PARAM$" 6.507 KB 14 rows
 16-AUG-13 18:14:43.529: . . exported "SYS"."TSDP_PARAMETER$" 5.929 KB 1 rows
 16-AUG-13 18:14:43.570: . . exported "SYS"."TSDP_POLICY$" 5.898 KB 1 rows
 16-AUG-13 18:14:43.618: . . exported "SYS"."TSDP_SUBPOL$" 6.304 KB 1 rows
 16-AUG-13 18:14:43.692: . . exported "SYSTEM"."REDO_DB" 23.42 KB 1 rows
 16-AUG-13 18:14:43.962: . . exported "WMSYS"."WM$ENV_VARS$" 6.054 KB 5 rows
 16-AUG-13 18:14:44.042: . . exported "WMSYS"."WM$EVENTS_INFO$" 5.789 KB 12 rows
 16-AUG-13 18:14:44.077: . . exported "WMSYS"."WM$HINT_TABLE$" 9.429 KB 75 rows
 16-AUG-13 18:14:44.124: . . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.351 KB 1 rows
 16-AUG-13 18:14:44.170: . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.960 KB 1 rows
 16-AUG-13 18:14:44.225: . . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.08 KB 1 rows
 16-AUG-13 18:14:44.274: . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 6.539 KB 8 rows
 16-AUG-13 18:14:44.281: . . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
 16-AUG-13 18:14:44.290: . . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
 16-AUG-13 18:14:44.296: . . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
 16-AUG-13 18:14:44.302: . . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
 16-AUG-13 18:14:44.308: . . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
 16-AUG-13 18:14:44.341: . . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
 16-AUG-13 18:14:44.351: . . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
 16-AUG-13 18:14:44.359: . . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
 16-AUG-13 18:14:44.366: . . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
 16-AUG-13 18:14:44.370: . . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
 16-AUG-13 18:14:44.410: . . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
 16-AUG-13 18:14:44.417: . . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
 16-AUG-13 18:14:44.424: . . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
 16-AUG-13 18:14:44.431: . . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
 16-AUG-13 18:14:44.438: . . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
 16-AUG-13 18:14:44.445: . . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
 16-AUG-13 18:14:44.452: . . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
 16-AUG-13 18:14:44.458: . . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
 16-AUG-13 18:14:44.466: . . exported "SYS"."AUD$" 0 KB 0 rows
 16-AUG-13 18:14:44.472: . . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
 16-AUG-13 18:14:44.480: . . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
 16-AUG-13 18:14:44.486: . . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
 16-AUG-13 18:14:44.494: . . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
 16-AUG-13 18:14:44.500: . . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
 16-AUG-13 18:14:44.507: . . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
 16-AUG-13 18:14:44.513: . . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
 16-AUG-13 18:14:44.518: . . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
 16-AUG-13 18:14:44.522: . . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
 16-AUG-13 18:14:44.529: . . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
 16-AUG-13 18:14:44.534: . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
 16-AUG-13 18:14:44.540: . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.544: . . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
 16-AUG-13 18:14:44.549: . . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
 16-AUG-13 18:14:44.554: . . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
 16-AUG-13 18:14:44.559: . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.566: . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.571: . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.579: . . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.590: . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.597: . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.604: . . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.611: . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.618: . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
 16-AUG-13 18:14:44.624: . . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
 16-AUG-13 18:14:44.631: . . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.639: . . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:44.645: . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
 16-AUG-13 18:14:47.053: . . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 8.640 KB 4 rows
 16-AUG-13 18:14:48.309: . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 10.18 KB 22 rows
 16-AUG-13 18:14:49.731: . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.929 KB 2 rows
 16-AUG-13 18:14:51.556: . . exported "SYS"."NACL$_ACE_EXP" 9.906 KB 1 rows
 16-AUG-13 18:14:52.456: . . exported "SYS"."NACL$_HOST_EXP" 6.890 KB 1 rows
 16-AUG-13 18:14:53.929: . . exported "WMSYS"."WM$EXP_MAP" 7.695 KB 3 rows
 16-AUG-13 18:14:54.029: . . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
 16-AUG-13 18:14:54.036: . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
 16-AUG-13 18:14:54.042: . . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
 16-AUG-13 18:14:54.048: . . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
 16-AUG-13 18:14:54.177: . . exported "SCOTT"."DEPT" 6 KB 4 rows
 16-AUG-13 18:14:54.225: . . exported "SCOTT"."EMP" 8.75 KB 14 rows
 16-AUG-13 18:14:54.267: . . exported "SCOTT"."SALGRADE" 5.929 KB 5 rows
 16-AUG-13 18:14:54.274: . . exported "SCOTT"."BONUS" 0 KB 0 rows
 16-AUG-13 18:14:56.693: Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
 16-AUG-13 18:14:56.709: ******************************************************************************
 16-AUG-13 18:14:56.710: Dump file set for SYS.SYS_EXPORT_FULL_01 is:
 16-AUG-13 18:14:56.715: /u01/app/oracle/dpdump/pythian/pythian_logging.dmp
 16-AUG-13 18:14:56.749: Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Fri Aug 16 18:14:56 2015 elapsed 0 00:03:25

The next snippet from a PYTHIAN user export offers an idea of where the power of this parameter may lie - discovering exactly where time is being spent for large and small schema objects:

16-AUG-13 18:28:12.983: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
 16-AUG-13 18:30:15.652: . . exported "PYTHIAN"."PMAST" 2.384 GB 23252224 rows
 16-AUG-13 18:30:16.388: . . exported "PYTHIAN"."LOC" 21.01 MB 199999 rows
 16-AUG-13 18:30:18.194: Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

DISABLE_ARCHIVE_LOGGING

This is one of the many options available with the TRANSFORM parameter to data pump import. This parameter may indeed be a dream come true for those very large datasets. Sometimes, the archived redo generated by import detracts from its speed and leaves many wondering why archived redo needs to be generated. Some of the more familiar TRANSFORM options that have been around since the dawn of the product are:

OID is used to force the assignment of new IDs for objects in the export file and not to attempt to reuse IDs during the import phase. The OID in the export file may clash with an ID in an existing object in the target schema, causing the object to be skipped.
SEGMENT_ATTRIBUTES is used to permit the placement of objects in a different tablespace from where they were exported. The physical, storage, and logging attributes of objects are ignored, and they inherit the characteristics as set for the target schema(s).
PCTSPACE is specified as a multiplier to be used for object extent requests and datafile sizes.

The DISABLE_ARCHIVE_LOGGING can be set globally or for indexes and/or tables tables. If set to Y, the logging attributes of the specified target are altered before it is imported. Then, they are reset to their original characteristics when the work completes. The parameter passed on the command-line can have three values:

transform=disable_archive_logging:Y
transform=disable_archive_logging:Y:table
transform=disable_archive_logging:Y:index

All import activities are logged unless one of the three options listed above is coded in the call to data pump import. The following listing illustrates the usage and output to completely disable a generation of archived redo.

 
 oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> impdp dumpfile=pythian.dmp table_exists_action=append schemas=pythian transform=disable_archive_logging:Y
 
 Import: Release 12.1.0.1.0 - Production on Sun Aug 18 05:47:14 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 Database Directory Object has defaulted to: "DPDUMP".
 Master table "SYS"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
 Starting "SYS"."SYS_IMPORT_SCHEMA_03": /******** AS SYSDBA dumpfile=pythian.dmp table_exists_action=append schemas=pythian transform=disable_archive_logging:Y 
 Processing object type SCHEMA_EXPORT/USER
 ORA-31684: Object type USER:"PYTHIAN" already exists
 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
 Processing object type SCHEMA_EXPORT/ROLE_GRANT
 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
 Processing object type SCHEMA_EXPORT/TABLE/TABLE
 Table "PYTHIAN"."LOC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
 Table "PYTHIAN"."PMAST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

ENCRYPTION_PWD_PROMPT

As we have heard so many times but for some reason seem to experience difficulty putting into practice: "Thou shalt not enter an encryption password on the command-line." This new-fangled parameter can force entry of this parameter manually when prompted. A data pump export of PYTHIAN.LOC with this parameter set to YES would proceed as follows:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> expdp dumpfile=pythian_loc.dmp tables=pythian.loc encryption_pwd_prompt=yes
 
 Export: Release 12.1.0.1.0 - Production on Sun Aug 18 05:55:04 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 Encryption Password:
 Database Directory Object has defaulted to: "DPDUMP".
 Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=pythian_loc.dmp tables=pythian.loc encryption_pwd_prompt=yes
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 50 MB
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 . . exported "PYTHIAN"."LOC" 42.00 MB 399998 rows
 Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
 /u01/app/oracle/dpdump/pythian/pythian_loc.dmp
 Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Aug 18 05:55:51 2013 elapsed 0 00:00:41

The ensuing import of that very same export would resemble the following (mistyped password entry deliberate):

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> impdp dumpfile=pythian_loc.dmp table_exists_action=replace tables=pythian.loc encryption_pwd_prompt=yes
 
 Import: Release 12.1.0.1.0 - Production on Sun Aug 18 05:59:33 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 Encryption Password: 
 Database Directory Object has defaulted to: "DPDUMP".
 ORA-39002: invalid operation
 ORA-39176: Encryption password is incorrect.

It would have been nice if it had asked for encryption password twice on export. Then it may not have been so "easy" for me to forget. Let's try that again...

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> impdp dumpfile=pythian_loc.dmp table_exists_action=replace tables=pythian.loc encryption_pwd_prompt=yes
 
 Import: Release 12.1.0.1.0 - Production on Sun Aug 18 06:06:19 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 Encryption Password: 
 Database Directory Object has defaulted to: "DPDUMP".
 Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
 Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA dumpfile=pythian_loc.dmp table_exists_action=replace tables=pythian.loc encryption_pwd_prompt=yes 
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 . . imported "PYTHIAN"."LOC" 42.00 MB 399998 rows
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sun Aug 18 06:06:55 2013 elapsed 0 00:00:30

One of the first questions I asked myself is how does/could this possibly work for unattended jobs where the parameter value is passed as YES:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> nohup impdp parfile=locimp.parfile &
 [1] 24311
 
 oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> nohup: ignoring input and appending output to `nohup.out'
 
 [1]+ Exit 1 nohup impdp parfile=locimp.parfile
 
 oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /u01/app/oracle/dpdump/pythian> cat n*out
 
 Import: Release 12.1.0.1.0 - Production on Sun Aug 18 06:09:33 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 Encryption Password: 
 ORA-39001: invalid argument value
 ORA-39207: Value NULL is invalid for parameter ENCRYPTION_PASSWORD.

Maybe I'll give Chuck a call and ask him to submit an enhancement request.

COMPRESSION_ALGORITHM

This enhancement is all about trade-offs, measuring resource consumption against compression ratio. Like many life experiences, you cannot have the two together. In other words, the higher the compression ratio, the more CPUs are required to pull it off. The values for this parameter are as follows:

BASIC offers the most efficient usage of CPU and effective compression ratio; it is deemed to be applicable to most sessions.
LOW favors size in comparison to CPU and yields a larger file size with a lower compression ratio.
MEDIUM is similar to BASIC. It uses a different algorithm as it performs the work at hand.
HIGH is a good choice when the size of the export file is the determining factor; on the source site, it yields the smallest file, but it could be the most CPU-intensive.

The Advanced Compression option must be installed to use this data pump parameter. The familiar dialogue with data pump export goes as follows:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /home/oracle> expdp dumpfile=pythian_pmast.dmp tables=pythian.pmast compression_algorithm=high
 
 Export: Release 12.1.0.1.0 - Production on Mon Aug 19 13:58:43 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=pythian_pmast.dmp tables=pythian.pmast compression_algorithm=high 
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 11.03 GB
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 . . exported "PYTHIAN"."PMAST" 9.536 GB 93008896 rows
 Master table "SYS"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYS.SYS_EXPORT_TABLE_04 is:
  /u01/app/oracle/dpdump/pythian/pythian_pmastlc.dmp
 Job "SYS"."SYS_EXPORT_TABLE_04" successfully completed at Mon Aug 19 14:25:45 2013 elapsed 0 00:08:20
 
 real 8m31.600s
 user 0m0.017s
 sys 0m0.026s
 
 Export: Release 12.1.0.1.0 - Production on Mon Aug 19 14:25:49 2013
 
 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
 
 Username: / as sysdba
 
 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 Database Directory Object has defaulted to: "DPDUMP".
 Starting "SYS"."SYS_EXPORT_TABLE_04": /******** AS SYSDBA dumpfile=pythian_pmasthc.dmp tables=pythian.pmast compression_algorithm=high 
 Estimate in progress using BLOCKS method...
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Total estimation using BLOCKS method: 11.03 GB
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 . . exported "PYTHIAN"."PMAST" 9.536 GB 93008896 rows
 Master table "SYS"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYS.SYS_EXPORT_TABLE_04 is:
  /u01/app/oracle/dpdump/pythian/pythian_pmasthc.dmp
 Job "SYS"."SYS_EXPORT_TABLE_04" successfully completed at Mon Aug 19 14:36:31 2013 elapsed 0 00:07:52
 
 real 10m45.764s
 user 0m0.018s
 sys 0m0.028s
 
 oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
 /home/oracle> ll /u01/app/oracle/dpdump/pythian
 total 20019600
 -rw-r-----. 1 oracle oinstall 10240008192 Aug 19 14:36 pythian_pmasthc.dmp
 -rw-r-----. 1 oracle oinstall 10240008192 Aug 19 14:25 pythian_pmastlc.dmp

Not surprisingly, since a relatively small amount of data as exported (a mere 11.03Gb of data), the difference in the export file sizes is not dramatic. The fact that the export with high compression took close to 25% longer and consumed close to 8% more "sys" time is not dramatic, but it offers a flavor of what this parameter can do for you.

Wrap-up

Many fondly remember the arrival of data pump with release 10 gR1. We tingled with this new-fangled tool to allow us to perform the ever-popular logical backup of Oracle database. We discovered the foundation of one of the underlying PL/SQL objects called DBMS_DATAPUMP and had an absolute field day as we rappelled into the depths of the product. Bear in mind: The trip is not over.