In this article we will discuss about Data Pump Enhancements in Oracle Database. NOLOGGING Option (DISABLE_ARCHIVE_LOGGING)The TRANSFORM parameter of impdp has been extended.
Data pump enhancements:-
NOLOGGING Option (DISABLE_ARCHIVE_LOGGING)
The TRANSFORM parameter of impdp has been extended to include a DISABLE_ARCHIVE_LOGGING option. The default setting of “N” has no any effect on logging behavior. 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.
The effect can be limited to a specific type of object (TABLE or INDEX) by appending the object type.
An example of its use is shown below.
$ impdp system/[email protected] 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.
LOGTIME parameter determines if timestamps should be included in the output messages from the
The allowable values are explained below.
NONE: The default value, which indicates that no timestamps should be included in the output.
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.
$ expdp scott/[email protected] tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log logtime=all
Export View as Table
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.
Now export the view using the
$ expdp scott/tiger views_as_tables=scott.emp_vdirectory=test_dir dumpfile=emp_v.dmp logfile=expdp_emp_v.log
Change Table Compression at Import
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.
$ impdp system/[email protected] directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log remap_schema=scott:test transform=table_compression_clause:compress
The allowable values for the TABLE_COMPRESSION_CLAUSE 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 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.
Change Table LOB Storage at Import.
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.
Below are allowable values.
- 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.
$ impdp system/[email protected] directory=test_dir dumpfile=lob_table.dmp logfile=impdp_lob_table.log transform=lob_storage:securefile
Dumpfile Compression Options.
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.
The meanings of the available values are described below.
- 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.
$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log compression=all compression_algorithm=medium
Encryption Password Enhancements.
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]
$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log encryption_pwd_prompt=yes
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
- Best and useful scripts for oracle database.
- Simple Steps to upgrade jre in r12.2.
- How to fix ORA-00020: maximum number of processes 150 exceeded
- How to fix Import Standard Purchase Orders Error cannot read the file PO_PDOI_GRP.start_process issue.
- How to fix ADOP Cutover failed PRE_CUTOVER_CHECKS_COMPLETED
- Data Pump (expdp, impdp) Enhancements in Oracle Base