相信大家都知道imp工具除了可以將備份檔案中的資料匯入到資料庫中之外，還可以只獲取備份檔案中包含的SQL語句。
不是很熟悉的朋友可以參考文章《【IMPDP】【IMP】SQL指令碼盡收眼底——SHOW引數與SQLFILE引數對比》（http://space.itpub.net/519536/viewspace-631290）。

本文希望給大家澄清一個事實：在備份檔案很大的情況下，獲取SQL語句與真實的資料匯入之間時間差距雖然是懸殊的，但獲取SQL過程的時間成本同樣不容忽視！

1.在sec使用者下模擬建立兩張大表
sec@ora10g> create table t1 as select * from all_objects;
sec@ora10g> insert into t1 select * from t1;
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /

2603584 rows created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select count(*) from t1;

COUNT(*)
----------
   5207168

sec@ora10g> create table t2 as select * from t1;

Table created.

2.生成sec使用者的備份資料
ora10g@secdb /db_backup$ exp sec/sec file=t.dmp log=t.log

Export: Release 10.2.0.1.0 - Production on Fri Apr 1 21:49:32 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC
About to export SEC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC's tables via Conventional Path ...
. . exporting table                             T1    5207168 rows exported
. . exporting table                             T2    5207168 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ora10g@secdb /db_backup$ du -sm t.dmp
1096    t.dmp

備份檔案大小1G。

3.真實匯入資料所需要的時間
1）刪除sec使用者中T1和T2表中的資料
sec@ora10g> truncate table t1;

Table truncated.

sec@ora10g> truncate table t2;

Table truncated.

2）準備匯入指令碼
ora10g@secdb /db_backup$ cat imp_t_DATA.sh
date
imp sec/sec file=t.dmp full=y ignore=y
date

3）匯入資料
ora10g@secdb /db_backup$ sh imp_t_DATA.sh
Fri Apr 1 21:54:19 CST 2011

Import: Release 10.2.0.1.0 - Production on Fri Apr 1 21:54:19 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. . importing table                           "T1"    5207168 rows imported
. . importing table                           "T2"    5207168 rows imported
Import terminated successfully without warnings.
Fri Apr 1 21:57:37 CST 2011

總用時3分18秒！

4.僅獲取備份檔案中SQL語句的時間
1）清理T1和T2表資料
sec@ora10g> truncate table t1;

Table truncated.

sec@ora10g> truncate table t2;

Table truncated.

2）準備生成SQL的指令碼
ora10g@secdb /db_backup$ cat imp_t_SQL.sh
date
imp sec/sec file=t.dmp full=y ignore=y show=y
date

3）獲取備份檔案中SQL指令碼
ora10g@secdb /db_backup$ sh imp_t_SQL.sh
Fri Apr 1 22:03:20 CST 2011

Import: Release 10.2.0.1.0 - Production on Fri Apr 1 22:03:20 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'ORA10G', inst_scn=>'1243333');"
"COMMIT; END;"
"CREATE TABLE "T1" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VARC"
"HAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER"
" NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CRE"
"ATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAM"
"P" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED""
" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 603979776 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
"OOL DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T1"

"CREATE TABLE "T2" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VARC"
"HAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER"
" NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CRE"
"ATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAM"
"P" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED""
" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 603979776 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
"OOL DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T2"

Import terminated successfully without warnings.
Fri Apr 1 22:03:40 CST 2011

比較開始和結束的時間，總用時20秒！

5.小結
20秒與3分18秒進行比較是比較懸殊的。這是顯然的，因為資料匯入的過程是真正的將資料寫入到了資料庫，而獲取備份檔案中的SQL指令碼的過程只是全面地讀取了一遍備份檔案而已。
但是，這裡要強調的是：這裡的20秒是可以被無限放大的，隨著備份檔案越來越大獲取SQL的整個過程也將是一個非常可觀的數值。不可小視。在使用這種方法讀取備份檔案資訊時請充分評估所需的時間。這不是一蹴而就便能完成的事情。

Good luck.

secooler
11.03.31

-- The End --

【IMP】使用imp工具的show選項獲取超大備份檔案中的SQL語句將非常耗時

相關文章