【IMP】使用IMP的SHOW参数轻松获取EXP命令的DUMP文件内容

使用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 --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-620943/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-620943/