extract SQL from dmp file？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: extract SQL from dmp file？
昨天跟惜分飞吃饭，期间谈到了恢复的场景中可能遇到需要从dmp文件获取表结构的情况，例如当你
使用ODU/dul等工具抽取数据进行恢复时，就需要相关的元数据，例如index的，view的等等一系列。
如果你有dmp且是完好的，那么容易，如何损坏了呢？
如果从oracle的dmp 文件获取里面的sql scripts呢，如果dmp文件是好的，那么很简单，如下：
—方法1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
[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/\n\n"; N++ } /\"$/ { prev="" if (N==0) next; s=index( $0, "\"" ); if ( s!=0 ) { printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 ) prev=substr($0,length($0)-1,1 ); } if (length($0)<78) printf( "\n" ); }' $* [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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
在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 "/\n"; } 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的脚本，谁改良下，记得告诉我，谢谢！
One Response to “extract SQL from dmp file？”
好东西，roger强啊
Leave a Reply
You must be logged in to post a comment.