Starting in Oracle 12c, we can disable ( well ... minimize) the logging by setting the new setting in the TRANFORM parameter . Logging is not totally eliminated but it will be minimal . This setting has no effect if the database is in FORCE LOGGING mode.
This is useful in loading large data sets or creating new databases . With this setting , you can minimize the logging for table or index or both.
if we look at the help at the command prompt ( impdp -help) , we can see there are few settings have added.
11g
TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.
12c
TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, LOB_STORAGE, OID, PCTSPACE,
SEGMENT_ATTRIBUTES, STORAGE, and TABLE_COMPRESSION_CLAUSE.
Here are the examples.
In the following example , the logging is disabled for both table and index.
C:\>impdp zahir schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y
Import: Release 12.1.0.1.0 - Production on Thu Dec 11 14:33:04 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Password:
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
Master table "ZAHIR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ZAHIR"."SYS_IMPORT_SCHEMA_01": zahir/******** schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TESTUSER" 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
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."ZAHIRTESTL" 12.50 MB 110350 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "ZAHIR"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Dec 11 14:33:18 2014 elapsed 0 00:00:11
In the following example , the logging is disabled for table.
C:\>impdp zahir schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y:TABLE
Import: Release 12.1.0.1.0 - Production on Thu Dec 11 14:34:27 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Password:
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
Master table "ZAHIR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ZAHIR"."SYS_IMPORT_SCHEMA_01": zahir/******** schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y:TABLE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TESTUSER" 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
ORA-39151: Table "TESTUSER"."ZAHIRTESTL" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "ZAHIR"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Thu Dec 11 14:34:31 2014 elapsed 0 00:00:01
In the following example , the logging is disabled for index.
C:\>impdp zahir schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y:INDEX
Import: Release 12.1.0.1.0 - Production on Thu Dec 11 14:34:44 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Password:
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
Master table "ZAHIR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ZAHIR"."SYS_IMPORT_SCHEMA_01": zahir/******** schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y:INDEX
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TESTUSER" 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
ORA-39151: Table "TESTUSER"."ZAHIRTESTL" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "ZAHIR"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Thu Dec 11 14:34:47 2014 elapsed 0 00:00:01
I welcome your comments.
No comments:
Post a Comment