sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle的dmp文件获取里面的sql scripts的方法

时间:2016-12-10 19:32   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库从dmp文件获取表结构的情况,例如当你使用ODU/dul等工具抽取数据进行恢复时,就需要相关的元数据,例如index的,view的等等一系列。

本站文章除注明转载外,均为本站原创: 转载自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(INITIALhttp://www.oracleplus.net 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"                                                                                                                                                                                                                                                                                                                                                                                                                                                 
最后UE编辑下即可,当然,如果你grep 这里还可以加上别的,例如produrece,package什么的。

补充:本人shell不咋地,上面来自orafaq的脚本,谁改良下,记得告诉我,谢谢!

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle的dmp文件获取里面的sql scripts的方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1385.html

Oracle研究中心

关键词:

从DMP文件中获取SQL scripts的笔记

Oracle获取相关的元数据