DBA Hub

📋Steps in this guide1/11

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

This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 1.

oracle 12cconfigurationintermediate
by OracleDba
16 views
1

NOLOGGING Option (DISABLE_ARCHIVE_LOGGING)

The parameter of has been extended to include a 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 before the data is imported and resetting it to once the operation is complete. The effect can be limited to a specific type of object ( or ) by appending the object type. An example of its use is shown below. The option has no effect if the database is running in FORCE LOGGING mode.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \
     remap_schema=scott:test
transform=disable_archive_logging:y
2

LOGTIME Parameter

The parameter determines if timestamps should be included in the output messages from the and utilities. The allowable values are explained below. - : The default value, which indicates that no timestamps should be included in the output, making the output look similar to that of previous versions. - : Timestamps are included in output to the console, but not in the associated log file. - : Timestamps are included in output to the log file, but not in the associated console messages. - : Timestamps are included in output to the log file and console. An example of the output is shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ 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

$
3

Export View as Table

The 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. To see it working, create a view. Now export the view using the parameter. By default 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 .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...

CONN scott/tiger@pdb1

CREATE VIEW emp_v AS 
  SELECT * FROM emp;

$ expdp scott/tiger
views_as_tables=scott.emp_v
directory=test_dir dumpfile=emp_v.dmp logfile=expdp_emp_v.log
4

Change Table Compression at Import

The clause of the parameter allows the table compression characteristics of the tables in an import to be altered on the fly. The allowable values for the include the following. - NONE : The table compression clause is omitted, so the table takes on the compression characteristics of the tablespace. - NOCOMPRESS : Disables table compression. - COMPRESS : Enables basic table compression. - ROW STORE COMPRESS BASIC : Same as COMPRESS. - ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as OLTP compression. - COLUMN STORE COMPRESS FOR QUERY : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances. - COLUMN STORE COMPRESS FOR ARCHIVE : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances. Compression clauses that contain whitespace must be enclosed by single or double quotes. An example of its use is shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE | compression_clause]

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \
     remap_schema=scott:test
transform=table_compression_clause:compress
5

Change Table LOB Storage at Import

The clause of the parameter allows the LOB storage characteristics of table columns in a non-transportable import to be altered on the fly. The allowable values for the clause include the following. - SECUREFILE : The LOBS are stored as SecureFiles. - BASICFILE : The LOBS are stored as BasicFiles. - DEFAULT : The LOB storage is determined by the database default. - NO_CHANGE : The LOB storage matches that of the source object. An example of its use is shown below.

Code/Command (click line numbers to comment):

1
2
3
4
TRANSFORM=LOB_STORAGE:[SECUREFILE | BASICFILE | DEFAULT | NO_CHANGE]

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=lob_table.dmp logfile=impdp_lob_table.log \
transform=lob_storage:securefile
6

Dumpfile Compression Options

As part of the Advanced Compression option, you can specify the parameter to determine the level of compression of the export dumpfile. This is not related to table compression discussed previously. - BASIC : The same compression algorithm used in previous versions. Provides good compression, without severely impacting on performance. - LOW : For use when reduced CPU utilisation is a priority over compression ratio. - MEDIUM : The recommended option. Similar characteristics to BASIC, but uses a different algorithm. - HIGH : Maximum available compression, but more CPU intensive. An example of its use is shown below.

Code/Command (click line numbers to comment):

1
2
3
4
COMPRESSION_ALGORITHM=[BASIC | LOW | MEDIUM | HIGH]

$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log \
compression=all compression_algorithm=medium
7

Multitenant Option Support (CDB and PDB)

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

Audit Commands

Oracle 12c allows data pump jobs to be audited by creating an audit policy. 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. Run the following data pump command. Checking the audit trail shows the data pump job was audited.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE AUDIT POLICY policy_name 
  ACTIONS COMPONENT=DATAPUMP [EXPORT | IMPORT | ALL];

CONN / AS SYSDBA
CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;
AUDIT POLICY audit_dp_all_policy BY scott;

$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log

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

Encryption Password Enhancements

In previous versions, data pump encryption required the parameter to be entered on the command line, making password snooping relatively easy. In Oracle 12c, the 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. An example of its use is shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
ENCRYPTION_PWD_PROMPT=[YES | NO]

$ 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

$
10

Transportable Database

The option can now be combined with the option to transport a whole database. This method can also be used to upgrade the database as described here .

Code/Command (click line numbers to comment):

1
2
3
4
$ expdp system/Password1
full=Y transportable=always
version=12 directory=TEMP_DIR \
   dumpfile=orcl.dmp logfile=expdporcl.log
11

Miscellaneous Enhancements

- Data Pump supports extended data types , provided the parameter is not set to a value prior to 12.1. - LOB columns with a domain index can now take advantage of the direct path load method. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!