昨天跟惜分飞吃饭，期间谈到了恢复的场景中可能遇到需要从dmp文件获取表结构的情况，例如当你
使用ODU/dul等工具抽取数据进行恢复时，就需要相关的元数据，例如index的，view的等等一系列。
如果你有dmp且是完好的，那么容易，如何损坏了呢？
如果从oracle的dmp 文件获取里面的sql scripts呢，如果dmp文件是好的，那么很简单，如下：
---方法1
最后直接UE编辑下就是一个可执行的完整sql脚本了。
---方法2
---方法3
补充：本人shell不咋地，上面来自orafaq的脚本，谁改良下，记得告诉我，谢谢！
使用ODU/dul等工具抽取数据进行恢复时，就需要相关的元数据，例如index的，view的等等一系列。
如果你有dmp且是完好的，那么容易，如何损坏了呢？
如果从oracle的dmp 文件获取里面的sql scripts呢，如果dmp文件是好的，那么很简单，如下：
---方法1
[oracle@10gasm ~]$ imp roger/roger file=a.dmp fromuser=roger touser=roger1 show=y log=sql.out
Import: Release 10.2.0.1.0 - Production on Sun Aug 19 08:27:23 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing ROGER's objects into ROGER1
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'10GASM.REGRESS.RDBMS.DEV.US.ORACLE.COM',"
" inst_scn=>'3582547');"
"COMMIT; END;"
"ALTER SESSION SET CURRENT_SCHEMA= "ROGER1""
"CREATE TABLE "IND_T" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
"LT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
. . skipping table "IND_T"
"CREATE INDEX "IDX_A" ON "IND_T" ("A" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 "
"STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TA"
"BLESPACE "ROGER" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "ROGER1""
"CREATE TABLE "ROGER" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
"NS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
"ULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
. . skipping table "ROGER"
"ALTER SESSION SET CURRENT_SCHEMA= "ROGER1""
"CREATE TABLE "T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOB"
"JECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJE"
"CT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VA"
"RCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARC"
"HAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEF"
"AULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
. . skipping table "T"
"ALTER SESSION SET CURRENT_SCHEMA= "ROGER1""
"CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER""
Import terminated successfully without warnings.
[oracle@10gasm ~]$ cat print_sql_from_dmp.sh
#!/bin/ksh
awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\
/\
\
"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\
" );
}' $*
[oracle@10gasm ~]$
[root@10gasm oracle]# sh print_sql_from_dmp.sh sql.out > sql.sql
[root@10gasm oracle]# strings sql.sql
ALTER SESSION SET CURRENT_SCHEMA= "ROGER1"
CREATE TABLE "IND_T" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(
INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
CREATE INDEX "IDX_A" ON "IND_T" ("A" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(
INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING
ALTER SESSION SET CURRENT_SCHEMA= "ROGER1"
CREATE TABLE "ROGER" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(
INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
ALTER SESSION SET CURRENT_SCHEMA= "ROGER1"
CREATE TABLE "T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6291456
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
ALTER SESSION SET CURRENT_SCHEMA= "ROGER1"
CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER"
最后直接UE编辑下就是一个可执行的完整sql脚本了。
---方法2
oracle@10gasm ~]$ imp roger/roger file=a.dmp fromuser=roger touser=roger1 indexfile=sqltext.log log=imp.log
Import: Release 10.2.0.1.0 - Production on Sun Aug 19 08:31:06 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . skipping table "IND_T"
. . skipping table "ROGER"
. . skipping table "T"
Import terminated successfully without warnings.
[oracle@10gasm ~]$ strings sqltext.log
REM CREATE TABLE "ROGER1"."IND_T" ("A" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS ;
REM ... 2 rows
CONNECT ROGER1;
CREATE INDEX "ROGER1"."IDX_A" ON "IND_T" ("A" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING ;
REM CREATE TABLE "ROGER1"."ROGER" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS ;
REM ... 2 rows
REM CREATE TABLE "ROGER1"."T" ("OWNER" VARCHAR2(30), "OBJECT_NAME"
REM VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER,
REM "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE,
REM "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7),
REM "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY"
REM VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS ;
REM ... 49745 rows
这种方式，完成以后，也需要进行人工干预的，也不是特别好。
前面两种方法都是要保证dmp文件是好的情况下，如果dmp文件坏了，但我们又需要里面的sql scripts？那怎么办呢？
说明：如果你是用的数据泵，那么可以用sqlfile参数。
---方法3
在orafaq网站找的一个shell脚本，extract sql from dmp，测试发现不好使，看来是需要
修改下才行。我这里就直接用grep命令了，其实稍微完整一下就是一个shell脚本了。
来自orafaq的脚本：
##############################################################
# Developer : Manoj Murumkar
# Date : 21-Apr-03
# Description : This script extracts SQL statements
# from export dump file.
# Set N=1 if you want the statement to be output.
# NOTE : Use gawk(GNU version) available on GNU
# site for best results.
###############################################################
// { N=0; }
/^CONNECT/ { N=0; }
/^CREATE SYNONYM / { N=0; }
/^CREATE SEQUENCE / { N=0; }
/^CREATE DATABASE LINK / { N=0; }
/^CREATE TABLE / { N=0; }
/^CREATE INDEX / { N=0; }
/^ALTER / { N=0; }
/^ANALYZE / { N=0; }
/^GRANT / { N=1; }
/^AUDIT / { N=0; }
N==1 { for (i=1; i<= NF; i++) addword($i);
printline();
printf "/\
";
}
function addword(w) {
if (length(line) + length(w) > 78)
printline()
line = line " " w
}
function printline () {
if (length (line) > 0) {
print substr(line,2) # removes leading blanks
line = ""
}
}
我这里就非常简单了，不要笑话，哈哈！
[oracle@10gasm ~]$ ./a.sh
[oracle@10gasm ~]$ cat a.sh
grep -a 'CREATE TABLE' roger.dmp > create_objects.sql
grep -a 'CREATE INDEX' roger.dmp >> create_objects.sql
grep -a 'CREATE VIEW' roger.dmp >> create_objects.sql
grep -a 'CREATE SYNONYM' roger.dmp >> create_objects.sql
grep -a 'CREATE SEQUENCE' roger.dmp >> create_objects.sql
grep -a 'CREATE FUNCTION' roger.dmp >> create_objects.sql
grep -a 'ALTER' roger.dmp >> create_objects.sql
[oracle@10gasm ~]$ cat create_objects.sql
CREATE TABLE "IND_T" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(
INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
CREATE TABLE "ROGER" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(
INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
CREATE TABLE "T" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
CREATE TABLE "TEST_OGG" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY"
VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS
CREATE INDEX "IDX_A" ON "IND_T" ("A" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(
INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING
CREATE SYNONYM "V$PARAMETER" FOR "SYS"."SHOW_HIDDEN_V$PARAMETER"
[oracle@10gasm ~]$
最后UE编辑下即可，当然，如果你grep 这里还可以加上别的，例如produrece，package什么的。
补充：本人shell不咋地，上面来自orafaq的脚本，谁改良下，记得告诉我，谢谢！
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
