使用IMP的SHOW引數可以輕鬆獲取EXP的DUMP檔案內容，透過讀取其內容我們可以獲得很多有價值的資訊。
簡單列兩點：
1.建立資料庫物件的SQL語句
我們可以從中得到非常詳細的資料庫物件建立SQL語句。在此基礎上如能做簡單的調整和排版，完全可以生成一份全面的SQL建立指令碼。

2.表空間資訊
雖然這個資訊也是透過SQL建立語句的形式體現的，但還是應該重點強調一下它的重要性。
因為，假設我們收到了一個透過EXP命令生成的DUMP檔案，這時萬萬不可直接使用這個檔案進行匯入，極有可能因為沒有事先建立必要的表空間而導致漫長的匯入過程被迫終止。因此在每次匯入操作開始之前都應該使用SHOW引數檢視一下DUMP的內容，做到未雨綢繆。

再多的描述也沒有透過實驗來得直接。下面我來透過一個簡單的實驗來演示一下這個IMP命令的SHOW選項魅力。供大家參考。
1.在使用者sec中簡單建立幾個常用的資料庫物件
1）連線到sec使用者
sys@ora10g> conn sec/sec
Connected.

2）建立表t
sec@ora10g> create table t (x number, y varchar2(10));

Table created.

3）建立序列s
sec@ora10g> create sequence s;

Sequence created.

4）建立檢視v_t
sec@ora10g> create view v_t as select * from t;

View created.

5）建立索引
sec@ora10g> create index i_t on t(x);

Index created.

6）透過user_objects檢視檢視一下sec使用者當前所有的資料庫物件資訊
sec@ora10g> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
T                              TABLE
S                              SEQUENCE
V_T                            VIEW
I_T                            INDEX

2.使用EXP命令生成sec使用者的DUMP檔案sec.dmf
ora10g@secDB /exp$ exp sec/sec file=sec.dmf

Export: Release 10.2.0.3.0 - Production on Sun Nov 29 19:07:54 2009

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified users ...
. 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                              T          0 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.

3.使用IMP命令的SHOW選項檢視sec.dmf檔案的內容
ora10g@secDB /exp$ imp secooler/secooler file=sec.dmf show=y log=sec.log fromuser=sec touser=secooler ignore=y

Import: Release 10.2.0.3.0 - Production on Sun Nov 29 19:10:42 2009

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SEC, not by you

import done in AL32UTF8 character set and UTF8 NCHAR character set
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'ORA10G', inst_scn=>'30932400');"
"COMMIT; END;"
"CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREME"
"NT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
"CREATE TABLE "T" ("X" NUMBER, "Y" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INIT"
"RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUF"
"FER_POOL DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T"

"CREATE INDEX "I_T" ON "T" ("X" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAG"
"E(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
"CE "TBS_SEC_D" LOGGING"
"CREATE FORCE VIEW "SECOOLER"."V_T"                        ("X","Y") AS "
"select "X","Y" from t"
Import terminated successfully without warnings.

驀然間，一種一覽無餘的的感動油然而生！慢慢體會吧。

4.SHOW選項在IMP命令幫助資訊中的描述
ora10g@secDB /exp$ imp -help
...
SHOW     just list file contents (N)
...

5.小結
IMP的這個預覽DUMP檔案方法非常的實用，需善加利用。

Good luck.

secooler
09.11.29

-- The End --

【IMP】使用IMP的SHOW引數輕鬆獲取EXP命令的DUMP檔案內容

相關文章