12c 資料泵匯入時禁止生成日誌的引數disable_archive_logging

snowdba發表於2015-02-26
Hive
早就希望資料泵匯入時能夠有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

盼望已久的功能終於來了，盼著哪天有12c資料庫的匯入工作真正嘗試一次。

全文完

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1442027/，如需轉載，請註明出處，否則將追究法律責任。

相關文章