从Export Dumpfile file 中读出DDL 语句

最新推荐文章于 2021-04-09 06:23:27 发布

cuanxiu7017

最新推荐文章于 2021-04-09 06:23:27 发布

阅读量150

点赞数

从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

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

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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/34596/viewspace-592246/