【IMPDP】【IMP】SQL脚本尽收眼底——SHOW参数与SQLFILE参数对比

IMPDP的SQLFILE参数与IMP的SHOW参数类似，可以读取到dump文件中的SQL语句。
简单对比一下这两个工具带给我们的乐趣。

1.在sec用户下先创建一个测试表T
sec@secooler> create table t as select * from all_objects;

Table created.

2.分别使用EXP和EXPDP工具生成两份dump文件
1）使用EXP生成备份dump文件
secooler@secDB /expdp$ exp sec/sec file=sec_exp.dmp

Export: Release 11.2.0.1.0 - Production on Sat Apr 3 11:19:51 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 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      71325 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.

2）使用EXPDP生成备份dump文件
secooler@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp

Export: Release 11.2.0.1.0 - Production on Sat Apr 3 11:21:43 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_SCHEMA_01":  sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
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/TABLE/TABLE
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/TABLE/COMMENT
. . exported "SEC"."T"                                   6.904 MB   71325 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
  /expdp/sec_expdp.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:23:08

3.使用IMP的SHOW和IMPDP的SQLFILE参数查看dump文件中的SQL内容
1）使用IMP的SHOW参数查看dump文件中的SQL
secooler@secDB /expdp$ imp sec/sec file=sec_exp.dmp full=y show=y

Import: Release 11.2.0.1.0 - Production on Sat Apr 3 11:33:05 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK 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=>'SECOOLER', inst_scn=>'9648713');"
 "COMMIT; END;"
 "CREATE TABLE "T" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VARCH"
 "AR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER "
 "NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREA"
 "TED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAMP"
 "" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" "
 "VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER NOT NULL ENABLE, ""
 "EDITION_NAME" VARCHAR2(30))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 "
 "STORAGE(INITIAL 9437184 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROU"
 "PS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "T"

Import terminated successfully without warnings.

可见，dump文件中仅仅记录了表T的创建语句，而且需要手工重新进行排版。

2）使用IMPDP的SQLFILE参数查看dump文件中的SQL
secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql

Import: Release 11.2.0.1.0 - Production on Sat Apr 3 11:38:01 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SEC"."SYS_SQL_FILE_FULL_01":  sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql
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/TABLE/TABLE
Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 11:38:05

此时，并没有在生成过程中打印出SQL语句，而是将dump所包含SQL信息直接写入到了SQLFILE参数所指定的sec_expdp.sql文件中。
查看一下生成的sec_expdp.sql文件内容。
secooler@secDB /expdp$ cat sec_expdp.sql
-- CONNECT SEC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "SEC" IDENTIFIED BY VALUES 'S:AE049953812DAE6CD7EC7A10E747EB7688FA697DB0B3725F009C705161FB;9EC74A4FC0A9E227'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SEC";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "SEC";

 GRANT "DBA" TO "SEC";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "SEC" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SEC

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'SECOOLER', inst_scn=>'9649101');
COMMIT;
END;
/

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SEC"."T"
   (    "OWNER" VARCHAR2(30 BYTE) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30 BYTE),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19 BYTE),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19 BYTE),
        "STATUS" VARCHAR2(7 BYTE),
        "TEMPORARY" VARCHAR2(1 BYTE),
        "GENERATED" VARCHAR2(1 BYTE),
        "SECONDARY" VARCHAR2(1 BYTE),
        "NAMESPACE" NUMBER NOT NULL ENABLE,
        "EDITION_NAME" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

此刻，大家是不是有一种“SQL脚本尽收眼底”的感觉。不仅语句结构清晰，而且包含的信息已经不仅仅是T表的创建语句，同时包含了用户的创建及授权语句（就是因为包含了用户的创建信息，因此在使用具有足够权限的用户完成导入时，即使sec用户不存在，也会在导入的过程中完成用户sec的创建）。

4.小结
在使用IMP和IMPDP工具时，建议将每一个参数进行一番研究，也许就在研究的过程中获得了意想不到的收获。
通过这个例子大家也许进一步真实的感受到了IMPDP与IMP相比强大之处。

Good luck.

secooler
10.04.03

-- The End --

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

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