12c 数据泵导入时禁止生成日志的参数disable_archive_logging

郑全

早就希望数据泵导入时能够有nologging的功能，省去很多归档日志的消耗。从Oracle Database 12c开始终于有了该功能，就是使用参数DISABLE_ARCHIVE_LOGGING。从字面上就能看出它的用处吧，禁用归档日志！

在执行导入操作时，对象的记录日志属性会被设置为NO，导入操作结束后日志属性还原。

1，采用压缩方式导出hr用户数据
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=hrc.dmp logfile=hrc.log schemas=hr compression=all

对比一下压缩前后的差距，不到一半的空间。
[oracle@snow ~]$ ll
total 64024
-rw-r--r--. 1 oracle oinstall      945 Feb  8 18:33 12cpfile.ora
-rw-r-----. 1 oracle oinstall   176128 Feb  9 16:09 20150226_1.dmp
-rw-r-----. 1 oracle oinstall     1549 Feb  9 16:09 20150226_1.log
-rw-r-----. 1 oracle oinstall   237568 Feb  9 16:00 20150226.dmp
-rw-r--r--. 1 oracle oinstall     1613 Feb  8 20:00 createdb.sql
drwxr-xr-x. 7 oracle oinstall     4096 Jun 10  2013 database
-rw-r-----. 1 oracle oinstall     1618 Feb  9 16:00 export.log
-rw-r--r--. 1 oracle oinstall      154 Feb  9 16:09 flashback.par
-rw-r-----. 1 oracle oinstall 64253952 Feb  9 09:56 full.dmp
-rw-r-----. 1 oracle oinstall    29419 Feb  9 10:04 full.log
-rw-r-----. 1 oracle oinstall   241664 Feb  9 16:43 hrc.dmp  <压缩数据
-rw-r-----. 1 oracle oinstall     2568 Feb  9 16:43 hrc.log
-rw-r-----. 1 oracle oinstall   577536 Feb  9 15:22 hr.dmp   <非压缩数据
-rw-r-----. 1 oracle oinstall     2549 Feb  9 15:22 hr.log
-rw-r-----. 1 oracle oinstall     1721 Feb  9 15:32 import.log
-rw-r--r--. 1 oracle oinstall       88 Feb  9 14:43 pro.par
[oracle@snow ~]$
[oracle@snow ~]$
[oracle@snow ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Feb 9 16:47:39 2015

Copyright (c) 1982, 2013, Oracle.  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

SYS@ora12c >drop user hr cascade;

User dropped.

SYS@ora12c >exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@snow ~]$

导入数据时开启transform=disable_archive_logging:Y 避免生成归档日志
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hrc.dmp \
> transform=disable_archive_logging:Y

Import: Release 12.1.0.1.0 - Production on Mon Feb 9 16:49:16 2015

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
Master table "DP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DP"."SYS_IMPORT_FULL_01":  dp/******** directory=dp_dir dumpfile=hrc.dmp transform=disable_archive_logging:Y
Processing object type SCHEMA_EXPORT/USER
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES"                            5.218 KB      25 rows
. . imported "HR"."DEPARTMENTS"                          5.437 KB      27 rows
. . imported "HR"."EMPLOYEES"                            8.773 KB     107 rows
. . imported "HR"."JOBS"                                 5.445 KB      19 rows
. . imported "HR"."JOB_HISTORY"                          5.304 KB      10 rows
. . imported "HR"."LOCATIONS"                            6.046 KB      23 rows
. . imported "HR"."REGIONS"                              4.851 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DP"."SYS_IMPORT_FULL_01" successfully completed at Mon Feb 9 16:49:29 2015 elapsed 0 00:00:11

但是，对create table,alter table，也要产生日志，create index不会产生日志。

郑全

这个功能不错哈。