Oracle 从Dump 文件里提取 DDL 语句 方法说明
发布日期:2021-10-24 03:36:25 浏览次数:1 分类:技术文章

本文共 9050 字,大约阅读时间需要 30 分钟。

有关Dump 文件的命令有exp/imp 和 expdp/impdp。 这四个命令之前都有整理过相关的文章。

 

 

 

 

 

 

 

对于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

 

REM CREATE TABLE "DVD"."T1" ("ID" NUMBER)PCTFREE 10 PCTUSED 40 INITRANS

REM 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1

REM BUFFER_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 的不显示。

 

 

[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表空间和数据文件的关系,否则拒绝申请

转载于:https://www.cnblogs.com/tianlesoftware/archive/2011/09/21/3609560.html

转载地址:https://blog.csdn.net/weixin_30687587/article/details/94975362 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:Java程序练习-计算2的N次方
下一篇:docker中使用nginx容器代理其他容器

发表评论

最新留言

网站不错 人气很旺了 加油
[***.192.178.218]2024年03月27日 22时54分40秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

grouping函数 mysql_sql聚合函数有哪些 2021-06-24
python os.walk如何不遍历隐藏文件_python 获取文件下所有文件或目录os.walk()的实例... 2021-06-24
python 股票估值_【中金固收·固收+】隐藏价值的角落:限售股AAP估值及Python实现方法(上)... 2019-04-21
java文档生成_Java文档自动生成 2019-04-21
java 共享目录_java 操作windows 共享目录方法介绍 2019-04-21
java 监控 宕机_JAVA监测tomcat是否宕机,控制重启 2019-04-21
catch that cow java_POJ3278——Catch That Cow 2019-04-21
java integer 不变模式_Java代码的变与不变 2019-04-21
java guava 使用_Java8-Guava实战示例 2019-04-21
python barrier option pricing_《Python金融数据分析》书内代码实战与讲解(二)金融衍生物定价... 2019-04-21
java自带工具_深入了解Java JDK自带工具,包括javac、jar、jstack等,实用~ 2019-04-21
gnome mysql client_解决MySQLWorkbenchgnome-keyring-daemon错误的方法分享 2019-04-21
java线程占用CPU_在windows下揪出java程序占用cpu很高的线程并完美解决 2019-04-21
java多态替换switch_使多态性无法解决那些switch / case语句的麻烦 2019-04-21
java httpclient 进度条_如何使用Apache HttpClient 4获取文件上传的进度条? 2019-04-21
下列不属于java语言特点的是_下列选项中,不属于Java语言特点的一项是( )。... 2019-04-21
java中小数的乘法_javascript的小数点乘法除法实例 2019-04-21
kappa一致性检验教程_SPSS在线_SPSSAU_Kappa一致性检验 2019-04-21
linux shell mysql备份_linux shell 备份mysql 数据库 2019-04-21
Java双向链表时间复杂度_链表是什么?有多少种链表?时间复杂度是? 2019-04-21