Oracle 从Dump 文件里提取 DDL 语句 方法说明

ucybgobfer

　　

　　

　　
　　有关Dump 文件的命令有exp/imp 和 expdp/impdp。 这四个命令之前都有整理过相关的文章。
　　
　　ORACLE EXP/IMP 说明
　　http://blog.csdn.net/tianlesoftware/article/details/4718366
　　
　　exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项
　　http://blog.csdn.net/tianlesoftware/article/details/6093973
　　
　　Oracle expdp/impdp 使用示例
　　http://blog.csdn.net/tianlesoftware/article/details/6260138
　　
　　Oracle 10g Data Pump Expdp/Impdp 详解
　　http://blog.csdn.net/tianlesoftware/article/details/4674224
　　
　　Oracle expdp/impdp 从高版本 到 低版本 示例
　　http://blog.csdn.net/tianlesoftware/article/details/6533421
　　
　　
　　对于Dump 文件，我们不能直接提取出Data数据，但是我们可以通过相关的参数，从Dump文件中提取出对应的DDL 语句。
　　（1）如果是导出导入（exp/imp）,那么是indexfile参数。
　　（2）如果是数据泵（expdp/impdp）,那么是sqlfile 参数。
　　
准备工作：
　　SYS@anqing1(rac1)> create user dvdidentified by dvd;
　　User created.
　　SYS@anqing1(rac1)> grant dba to dvd;
　　Grant succeeded.
　　SYS@anqing1(rac1)> conn dvd/dvd;
　　Connected.
　　DVD@anqing1(rac1)> create table t1(idnumber);
　　Table created.
　　DVD@anqing1(rac1)> insert into t1values(1);
　　1 row created.
　　DVD@anqing1(rac1)> commit;
　　Commit complete.
　　DVD@anqing1(rac1)> create index idx_t1on t1(id);
　　Index created.
　　DVD@anqing1(rac1)>
　　
　　
一.使用导出导入命令
　　
1.1 导出dvd 用户的数据，生成dump文件
　　[oracle@rac1 ~]$ exp dvd/dvd file=dvd.dmpowner=dvd
　　
　　Export: Release 10.2.0.4.0 - Production onWed Sep 21 19:50:14 2011
　　
　　Copyright (c) 1982, 2007, Oracle. All rights reserved.
　　
　　
　　Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
　　With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
　　and Real Application Testing options
　　Export done in US7ASCII character set andAL16UTF16 NCHAR character set
　　server uses ZHS16GBK character set(possible charset conversion)
　　
　　About to export specified users ...
　　. exporting pre-schema procedural objectsand actions
　　. exporting foreign function library namesfor user DVD
　　. exporting PUBLIC type synonyms
　　. exporting private type synonyms
　　. exporting object type definitions foruser DVD
　　About to export DVD's objects ...
　　. exporting database links
　　. exporting sequence numbers
　　. exporting cluster definitions
　　. about to export DVD's tables via ConventionalPath ...
　　. . exporting table T1 1 rows exported
　　EXP-00091: Exporting questionablestatistics.
　　. exporting synonyms
　　. exporting views
　　. exporting stored procedures
　　. exporting operators
　　. exporting referential integrityconstraints
　　. exporting triggers
　　. exporting indextypes
　　. exporting bitmap, functional andextensible indexes
　　. exporting posttables actions
　　. exporting materialized views
　　. exporting snapshot logs
　　. exporting job queues
　　. exporting refresh groups and children
　　. exporting dimensions
　　. exporting post-schema procedural objectsand actions
　　. exporting statistics
　　Export terminated successfully withwarnings.
　　
1.2 从dump 文件里提取DDL语句
　　
　　[oracle@rac1 ~]$ imp dvd/dvd file=dvd.dmpfromuser=dvd touser=dvd indexfile=dvd.sql
　　
　　Import: Release 10.2.0.4.0 - Production onWed Sep 21 19:50:50 2011
　　
　　Copyright (c) 1982, 2007, Oracle. All rights reserved.
　　
　　
　　Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
　　With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
　　and Real Application Testing options
　　
　　Export file created by EXPORT:V10.02.01 viaconventional path
　　import done in US7ASCII character set andAL16UTF16 NCHAR character set
　　import server uses ZHS16GBK character set (possiblecharset conversion)
　　. . skipping table "T1"
　　
　　Import terminated successfully withoutwarnings.
　　
　　这里要注意2点：
　　（1） 该import 命令并没有真正的import data，而只是生成了我们对应用户下所有DDL的sql 语句。
　　（2） 对于表的DDL语句，用REM 进行了注释。
　　
　　[oracle@rac1 ~]$ cat dvd.sql
　　
　　REMCREATE TABLE "DVD"."T1" ("ID" NUMBER)PCTFREE 10 PCTUSED 40 INITRANS
　　REM1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
　　REMBUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
　　REM... 1 rows
　　CONNECT DVD;
　　CREATE INDEX "DVD"."IDX_T1"ON "T1" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS
　　255 STORAGE(INITIAL 65536 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL
　　DEFAULT) TABLESPACE "USERS"LOGGING ;
　　
　　如果只想看索引的DDL，那么可以用grep命令，讲REM 的不显示。
　　
　　Linux Grep 命令说明
　　http://blog.csdn.net/tianlesoftware/article/details/6277193
　　
　　[oracle@rac1 ~]$ cat dvd.sql|grep -v REM
　　
　　CONNECT DVD;
　　CREATE INDEX"DVD"."IDX_T1" ON "T1" ("ID" ) PCTFREE10 INITRANS 2 MAXTRANS
　　255 STORAGE(INITIAL 65536 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL
　　DEFAULT) TABLESPACE "USERS"LOGGING ;
　　
　　
二.数据泵（expdp/impdp）
　　
2.1 导出dvd用户的数据
　　[oracle@rac1 ~]$ expdp dvd/dvddirectory=backup dumpfile=dvd.dmp schemas=dvd
　　
　　Export: Release 10.2.0.4.0 - Production onWednesday, 21 September, 2011 20:16:59
　　
　　Copyright (c) 2003, 2007, Oracle. All rights reserved.
　　
　　Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
　　With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
　　and Real Application Testing options
　　FLASHBACK automatically enabled to preservedatabase integrity.
　　Starting "DVD"."SYS_EXPORT_SCHEMA_01": dvd/******** directory=backupdumpfile=dvd.dmp schemas=dvd
　　Estimate in progress using BLOCKS method...
　　Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATA
　　Total estimation using BLOCKS method: 64 KB
　　Processing object type SCHEMA_EXPORT/USER
　　Processing object typeSCHEMA_EXPORT/SYSTEM_GRANT
　　Processing object typeSCHEMA_EXPORT/ROLE_GRANT
　　Processing object typeSCHEMA_EXPORT/DEFAULT_ROLE
　　Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
　　Processing object type SCHEMA_EXPORT/TABLE/TABLE
　　Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
　　Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
　　Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
　　Processing object type SCHEMA_EXPORT/TABLE/COMMENT
　　. . exported"DVD"."T1" 4.906 KB 1 rows
　　Master table"DVD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
　　******************************************************************************
　　Dump file set for DVD.SYS_EXPORT_SCHEMA_01is:
　　/u01/backup/dvd.dmp
　　Job"DVD"."SYS_EXPORT_SCHEMA_01" successfully completed at20:17:34
　　
　　
2.2 产生DDL
　　
　　[oracle@rac1 ~]$ impdp dvd/dvddirectory=backup dumpfile=dvd.dmp sqlfile=dvd.sql
　　
　　Import: Release 10.2.0.4.0 - Production onWednesday, 21 September, 2011 20:18:50
　　
　　Copyright (c) 2003, 2007, Oracle. All rights reserved.
　　
　　Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
　　With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
　　and Real Application Testing options
　　Master table"DVD"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
　　Starting"DVD"."SYS_SQL_FILE_FULL_01": dvd/******** directory=backupdumpfile=dvd.dmp sqlfile=dvd.sql
　　Processing object type SCHEMA_EXPORT/USER
　　Processing object typeSCHEMA_EXPORT/SYSTEM_GRANT
　　Processing object typeSCHEMA_EXPORT/ROLE_GRANT
　　Processing object typeSCHEMA_EXPORT/DEFAULT_ROLE
　　Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
　　Processing object typeSCHEMA_EXPORT/TABLE/TABLE
　　Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
　　Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
　　Job"DVD"."SYS_SQL_FILE_FULL_01" successfully completed at20:18:54
　　
2.3 查看DDL 文本
　　
　　[oracle@rac1 backup]$ cat dvd.sql
　　-- CONNECT DVD
　　-- new object type path is:SCHEMA_EXPORT/USER
　　-- CONNECT SYSTEM
　　CREATE USER "DVD" IDENTIFIED BYVALUES '1111602792579CCE'
　　DEFAULT TABLESPACE "USERS"
　　TEMPORARY TABLESPACE "TEMP";
　　
　　-- new object type path is:SCHEMA_EXPORT/SYSTEM_GRANT
　　GRANT UNLIMITED TABLESPACE TO"DVD";
　　
　　-- new object type path is:SCHEMA_EXPORT/ROLE_GRANT
　　GRANT "DBA" TO "DVD";
　　
　　-- new object type path is:SCHEMA_EXPORT/DEFAULT_ROLE
　　ALTER USER "DVD" DEFAULT ROLE ALL;
　　
　　-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
　　-- CONNECT DVD
　　
　　BEGIN
　　sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),export_db_name=>'ANQING.REGRESS.RDBMS.DEV.US.ORACLE.COM',inst_scn=>'9530068');
　　COMMIT;
　　END;
　　/
　　
　　-- new object type path is:SCHEMA_EXPORT/TABLE/TABLE
　　CREATE TABLE "DVD"."T1"
　　( "ID" NUMBER
　　)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)
　　TABLESPACE "USERS" ;
　　
　　-- new object type path is:SCHEMA_EXPORT/TABLE/INDEX/INDEX
　　CREATE INDEX"DVD"."IDX_T1" ON "DVD"."T1"("ID")
　　PCTFREE 10 INITRANS 2 MAXTRANS 255
　　STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
　　PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
　　TABLESPACE "USERS" PARALLEL 1 ;
　　
　　ALTER INDEX "DVD"."IDX_T1" NOPARALLEL;
　　
　　-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
　　DECLARE IND_NAME VARCHAR2(60);
　　IND_OWNER VARCHAR2(60);
　　BEGIN
　　DELETE FROM "SYS"."IMPDP_STATS";
　　IND_NAME := 'IDX_T1'; IND_OWNER:= 'DVD';
　　INSERT INTO "SYS"."IMPDP_STATS" (type, version,flags, c1, c2, c3, c5,
　　n1, n2, n3, n4, n5, n6, n7, n8,n9, n10, n11, n12, d1)
　　VALUES ('I', 4, 0, IND_NAME, NULL, NULL, 'DVD', 1, 1, 1, 1, 1, 1, 0, 1,NULL, NULL, NULL, NULL, TO_DATE('2011-09-21 19:45:20','YYYY-MM-DD:HH24:MI:SS'));
　　
　　DBMS_STATS.IMPORT_INDEX_STATS( '"' || ind_owner || '"','"' || ind_name || '"', NULL, '"IMPDP_STATS"', NULL,'"SYS"');
　　DELETE FROM "SYS"."IMPDP_STATS";
　　END;
　　/
　　
　　从exp/imp 与 expdp/impdp 的DDL 结果进行对比，expdp/impdp 提取DDL 语句的更详细，可读性要好很多。
　　
　　
　　
　　
　　-------------------------------------------------------------------------------------------------------
　　Blog： http://blog.csdn.net/tianlesoftware
　　Weibo: http://weibo.com/tianlesoftware
　　Email: dvd.dba@gmail.com
　　DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)
　　DBA 超级群：63306533(满); DBA4 群：83829929(满) DBA5群： 142216823(满)
　　DBA6 群：158654907(满) DBA7 群：69087192(满)DBA8 群：172855474
　　DBA 超级群2：151508914 DBA9群：102954821 聊天 群：40132017(满)
　　--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请