ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 从Export Dumpfile file 中读出DDL 语句

从Export Dumpfile file 中读出DDL 语句

原创 Linux操作系统 作者:myhuaer 时间:2009-04-29 14:34:02 0 删除 编辑
从Export Dumpfile 读出DDL 语句:
 
EXPDP/IMPDP  
 
[oracle@itc-test9 exp_dir]expdp system/ocm DIRECTORY=my_dump DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott
 
Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 29 April, 2009 14:06:51
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********/ DIRECTORY=my_dump DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
.....
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /disk/exp_dir/expdp_scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:07:11
 
[oracle@itc-test9 exp_dir]impdp system/ocm DIRECTORY=my_dump DUMPFILE=expdp_scott.dmp NOLOGFILE=y SQLFILE=impdp_scott.sql FULL=y
 
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 29 April, 2009 14:07:56
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/********/ DIRECTORY=my_dump DUMPFILE=expdp_scott.dmp NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y
Processing object type SCHEMA_EXPORT/USER
......
 
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 14:07:59
 
[oracle@itc-test9 exp_dir]more impdp_scott.sql
-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/USER
 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK;
 
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
 
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "SCOTT";
 
 GRANT "RESOURCE" TO "SCOTT";
 
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "SCOTT" DEFAULT ROLE ALL;
 
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT
。。。。
 
《上面的内容就是此dumpfile 中的DDL 语句》
 
 
EXP/IMP  

[oracle@itc-test9 exp_dir]exp system/ocm FILE=exp_scott.dmp LOG=exp_scott.log WNER=scott
 
Export: Release 10.2.0.1.0 - Production on Wed Apr 29 14:14:34 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 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 SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 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.
[oracle@itc-test9 exp_dir]imp system/ocm FILE=exp_scott.dmp LOG=imp_scott.log FULL=y SHOW=y
 
Import: Release 10.2.0.1.0 - Production on Wed Apr 29 14:14:45 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing SCOTT's objects into SCOTT
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'OCM.REGRESS.RDBMS.DEV.US.ORACLE.COM', in"
 "st_scn=>'940611');"
 "COMMIT; END;"
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
 ", "COMM" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"
 "SERS" LOGGING NOCOMPRESS"
. . skipping table "BONUS"
 
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR"
 "CHAR2(13))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6"
 "5536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" "
 "LOGGING NOCOMPRESS"
. . skipping table "DEPT"
 
 "CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO" )  PCTFREE 10 INITRANS 2 "
 "MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
 "L DEFAULT) TABLESPACE "USERS" LOGGING"
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "ALTER TABLE "DEPT" ADD  CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING I"
 "NDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F"
 "REELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE "
 "CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"
 "AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"
 "BER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
 "S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
 "LT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "EMP"
 
 "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" )  PCTFREE 10 INITRANS 2 MAX"
 "TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D"
 "EFAULT) TABLESPACE "USERS" LOGGING"
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "ALTER TABLE "EMP" ADD  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"
 "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE"
 "LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE "
 "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER)  P"
 "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIST"
 "S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOM"
 "PRESS"
. . skipping table "SALGRADE"
 
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
 "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
 "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully without warnings.
[oracle@itc-test9 exp_dir]
 
《上面的内容就是此dumpfile 中的DDL 语句》

这个方法可以使你的dump file 对你是透明的,在导入之前你就知道需要导入那些对象,那些权限,创建在那些TS 上。
 
 

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

下一篇: Oracle OMF 为鸡肋
请登录后发表评论 登录
全部评论

注册时间:2016-09-11

  • 博文量
    211
  • 访问量
    629986