ITPub博客

首页 > Linux操作系统 > Linux操作系统 > extract SQL from dmp file?

extract SQL from dmp file?

原创 Linux操作系统 作者:ciwei_no1 时间:2012-08-24 17:05:32 0 删除 编辑

本站文章除注明转载外,均为本站原创:

转载自love wife & love life —Roger 提供oracle技术支持服务

本文链接地址: extract SQL from dmp file?

昨天跟惜分飞吃饭,期间谈到了恢复的场景中可能遇到需要从dmp文件获取表结构的情况,例如当你
使用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/\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

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 "/\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的脚本,谁改良下,记得告诉我,谢谢!

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    24
  • 访问量
    55556