Oracle 從Dump 檔案裡提取 DDL 語句 方法說明

有關Dump 檔案的命令有exp/imp 和 expdp/impdp。 這四個命令之前都有整理過相關的文章。

Oracle EXP/IMP 說明

exp/imp 與 expdp/impdp 對比 及使用中的一些最佳化事項

Oracle expdp/impdp 使用樣本

Oracle 10g Data Pump Expdp/Impdp 詳解

Oracle 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 的不顯示。

 

Linux Grep 命令說明

 

[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 ;

1 2 下一頁