Oracle Data Pump was introduced in Oracle 10g. This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 1, including the following.
Related articles.
The TRANSFORM
parameter of impdp
has been extended to include a DISABLE_ARCHIVE_LOGGING
option. The default setting of "N" has no affect on logging behaviour. Using a value "Y" reduces the logging associated with tables and indexes during the import by setting their logging attribute to NOLOGGING
before the data is imported and resetting it to LOGGING
once the operation is complete.
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
The effect can be limited to a specific type of object (TABLE
or INDEX
) by appending the object type.
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX
An example of its use is shown below.
$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \ remap_schema=scott:test transform=disable_archive_logging:y
The DISABLE_ARCHIVE_LOGGING
option has no effect if the database is running in FORCE LOGGING mode.
The LOGTIME
parameter determines if timestamps should be included in the output messages from the expdp
and impdp
utilities.
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
The allowable values are explained below.
NONE
: The default value, which indicates that no timestamps should be included in the output, making the output look similar to that of previous versions.STATUS
: Timestamps are included in output to the console, but not in the associated log file.LOGFILE
: Timestamps are included in output to the log file, but not in the associated console messages.ALL
: Timestamps are included in output to the log file and console.An example of the output is shown below.
$ expdp scott/tiger@pdb1 tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log logtime=all Export: Release 12.1.0.1.0 - Production on Wed Nov 20 22:11:57 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, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options 20-NOV-13 22:12:09.312: Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@pdb1 tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log logtime=all 20-NOV-13 22:12:13.602: Estimate in progress using BLOCKS method... 20-NOV-13 22:12:17.797: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 20-NOV-13 22:12:18.145: Total estimation using BLOCKS method: 64 KB 20-NOV-13 22:12:30.583: Processing object type TABLE_EXPORT/TABLE/TABLE 20-NOV-13 22:12:33.649: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX 20-NOV-13 22:12:37.744: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 20-NOV-13 22:12:38.065: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 20-NOV-13 22:12:38.723: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 20-NOV-13 22:12:41.052: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 20-NOV-13 22:12:41.337: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER 20-NOV-13 22:13:38.255: . . exported "SCOTT"."EMP" 8.75 KB 14 rows 20-NOV-13 22:13:40.483: Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 20-NOV-13 22:13:40.507: ****************************************************************************** 20-NOV-13 22:13:40.518: Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: 20-NOV-13 22:13:40.545: /home/oracle/emp.dmp 20-NOV-13 22:13:40.677: Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 20 22:13:40 2013 elapsed 0 00:01:36 $
The VIEWS_AS_TABLES
parameter allows Data Pump to export the specified views as if they were tables. The table structure matches the view columns, with the data being the rows returned by the query supporting the views.
VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...
To see it working, create a view.
CONN scott/tiger@pdb1 CREATE VIEW emp_v AS SELECT * FROM emp;
Now export the view using the VIEWS_AS_TABLES
parameter.
$ expdp scott/tiger views_as_tables=scott.emp_v directory=test_dir dumpfile=emp_v.dmp logfile=expdp_emp_v.log
By default expdp
creates a temporary table as a copy of the view, but with no data, to provide a source of the metadata for the export. Alternatively to can specify a table with the appropriate structure. This probably only makes sense if you are using this functionality in a read-only database.
The are a number of restrictions relating to this parameter, which you can read about here.
The TABLE_COMPRESSION_CLAUSE
clause of the TRANSFORM
parameter allows the table compression characteristics of the tables in an import to be altered on the fly.
TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE | compression_clause]
The allowable values for the TABLE_COMPRESSION_CLAUSE
include the following.
Compression clauses that contain whitespace must be enclosed by single or double quotes.
An example of its use is shown below.
$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \ remap_schema=scott:test transform=table_compression_clause:compress
The LOB_STORAGE
clause of the TRANSFORM
parameter allows the LOB storage characteristics of table columns in a non-transportable import to be altered on the fly.
TRANSFORM=LOB_STORAGE:[SECUREFILE | BASICFILE | DEFAULT | NO_CHANGE]
The allowable values for the LOB_STORAGE
clause include the following.
An example of its use is shown below.
$ impdp system/Password1@pdb1 directory=test_dir dumpfile=lob_table.dmp logfile=impdp_lob_table.log \ transform=lob_storage:securefile
As part of the Advanced Compression option, you can specify the COMPRESSION_ALGORITHM
parameter to determine the level of compression of the export dumpfile. This is not related to table compression discussed previously.
COMPRESSION_ALGORITHM=[BASIC | LOW | MEDIUM | HIGH]The meanings of the available values are described below.
An example of its use is shown below.
$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log \ compression=all compression_algorithm=medium
Oracle Database 12c introduced the multitenant option, allowing multiple pluggable databases (PDBs) to reside in a single container database (CDB). For the most part, using data pump against a PDB is indistinguishable from using it against a non-CDB instance.
Exports using the FULL
option from 11.2.0.2 or higher can be imported into a clean PDB in the same way you would expect for a regular full import.
There are some minor restrictions, which you can read about here.
Oracle 12c allows data pump jobs to be audited by creating an audit policy.
CREATE AUDIT POLICY policy_name ACTIONS COMPONENT=DATAPUMP [EXPORT | IMPORT | ALL];
When this policy is applied to a user, their data pump jobs will appear in the audit trail. The following policy audits all data pump operations. The policy is applied to the SCOTT user.
CONN / AS SYSDBA CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL; AUDIT POLICY audit_dp_all_policy BY scott;
Run the following data pump command.
$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log
Checking the audit trail shows the data pump job was audited.
-- Flush audit information to disk. EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; SET LINESIZE 200 COLUMN event_timestamp FORMAT A30 COLUMN dp_text_parameters1 FORMAT A30 COLUMN dp_boolean_parameters1 FORMAT A30 SELECT event_timestamp, dp_text_parameters1, dp_boolean_parameters1 FROM unified_audit_trail WHERE audit_type = 'Datapump'; EVENT_TIMESTAMP DP_TEXT_PARAMETERS1 DP_BOOLEAN_PARAMETERS1 ------------------------------ ------------------------------ ------------------------------ 14-DEC-13 09.47.40.098637 PM MASTER TABLE: "SCOTT"."SYS_EX MASTER_ONLY: FALSE, DATA_ONLY: PORT_TABLE_01" , JOB_TYPE: EXP FALSE, METADATA_ONLY: FALSE, ORT, METADATA_JOB_MODE: TABLE_ DUMPFILE_PRESENT: TRUE, JOB_RE EXPORT, JOB VERSION: 12.1.0.0. STARTED: FALSE 0, ACCESS METHOD: AUTOMATIC, D ATA OPTIONS: 0, DUMPER DIRECTO RY: NULL REMOTE LINK: NULL, T ABLE EXISTS: NULL, PARTITION O PTIONS: NONE SQL>
In previous versions, data pump encryption required the ENCRYPTION_PASSWORD
parameter to be entered on the command line, making password snooping relatively easy.
In Oracle 12c, the ENCRYPTION_PWD_PROMPT
parameter enables encryption without requiring the password to be entered as a command line parameter. Instead, the user is prompted for the password at runtime, with their response not echoed to the screen.
ENCRYPTION_PWD_PROMPT=[YES | NO]
An example of its use is shown below.
$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log \ encryption_pwd_prompt=yes Export: Release 12.1.0.1.0 - Production on Sat Dec 14 21:09:11 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: Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log encryption_pwd_prompt=yes Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION . . exported "SCOTT"."EMP" 8.765 KB 14 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/emp.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Dec 14 21:09:55 2013 elapsed 0 00:00:41 $
The TRANSPORTABLE
option can now be combined with the FULL
option to transport a whole database.
$ expdp system/Password1 full=Y transportable=always version=12 directory=TEMP_DIR \ dumpfile=orcl.dmp logfile=expdporcl.log
This method can also be used to upgrade the database as described here.
VERSION
parameter is not set to a value prior to 12.1.For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/12c/data-pump-enhancements-12cr1