通過shell解析dump生成parfile

當我們得到一個dump檔案的時候，總是有些不太确定dump檔案中是否含有一些我們原本不希望出現的表，如果在未知的情況下對dump檔案進行操作時很危險的，比如我們想要得到的是表結構的資訊。如果不指定需要的表，會把目前schema下所有的Object對象的資訊都導出。一般的操作中，建議還是使用parfile。

[ora11g@rac1 dbm_lite]$ exp n1/n1 file=n1_ddl.dmp rows=n  statistics=none

Export: Release 11.2.0.3.0 - Production on Tue Aug 26 07:21:57 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user N1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user N1

About to export N1's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export N1's tables via Conventional Path ...

. . exporting table                     BIG_INSERT

. . exporting table                BIG_INSERT_EXT2

. . exporting table                      CLOB_TEST

. . exporting table                 CLOB_TEST_EXT2

. . exporting table                           DATA

. . exporting table                       NEW_DATA

. . exporting table                       OLD_DATA

. . exporting table                       PAR_DATA

. . exporting table                    RATED_EVENT

. . exporting table                   SMALL_INSERT

. . exporting table              SMALL_INSERT_EXT2

. . exporting table                              T

. . exporting table                             T1

. . exporting table                             T2

. . exporting table                      TEMP_TEST

. . exporting table                 TEMP_TEST_EXT2

. . exporting table                           TEST

. . exporting table                      TEST_DATA

. . exporting table                 TEST_DATA_EXT2

. . exporting table                      TEST_EXT2

. . exporting table                             TT

. . exporting table                            TTT

. . exporting table                        TT_EXT2

. . exporting table                         T_EXT2

. . exporting table                         T_TEMP

. . exporting table                    T_TEMP_EXT2

. . exporting table                       WIN_LIST

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

但是很多時候我們都因為是dump檔案，感覺一下子沒有了方向。無從查起。

如果我們能夠得到dump檔案中的table清單，那就太好了，我們可以很安全的進行資料的操作。不會稀裡糊塗的把一些“未知”操作避免。

如果要解析dump檔案，之前分享過一篇日志，通過awk來解析dump檔案，得到對應的sql語句，現在來通過一些簡單的指令解析dump檔案。

我簡單測試過百兆，數十G的dump檔案，這個指令的速度都還可以，

首先，我們得到一個比較粗略的table list

[ora11g@rac1 dbm_lite]$ strings n1_ddl.dmp|grep "CREATE TABLE"|awk '{print $3}'

"BIG_INSERT"

"CLOB_TEST"

"DATA"

"NEW_DATA"

"OLD_DATA"

"PAR_DATA"

"RATED_EVENT"

"SMALL_INSERT"

"T"

"T1"

"T2"

"TEMP_TEST"

"TEST"

"TEST_DATA"

"TT"

"TTT"

"T_TEMP"

"WIN_LIST"

得到的内容如上所示，我們需要去除雙引号，然後加入tables=,每行末尾加入一個逗号。

先去掉雙引号

[ora11g@rac1 dbm_lite]$ strings n1_ddl.dmp|grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'

BIG_INSERT

CLOB_TEST

DATA

NEW_DATA

OLD_DATA

PAR_DATA

RATED_EVENT

SMALL_INSERT

T

T1

T2

TEMP_TEST

TEST

TEST_DATA

TT

TTT

T_TEMP

WIN_LIST

然後再次判斷，如果是第一行就加入字首tables=，否則加入字首一個逗号，這樣就不用麻煩的判斷最後一行的逗号是不是要加了。

[ora11g@rac1 dbm_lite]$  strings n1_ddl.dmp|grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'|awk '{ if(FNR==1) print "tables="$1 ; else print ","$1}'

tables=BIG_INSERT

,CLOB_TEST

,DATA

,NEW_DATA

,OLD_DATA

,PAR_DATA

,RATED_EVENT

,SMALL_INSERT

,T

,T1

,T2

,TEMP_TEST

,TEST

,TEST_DATA

,TT

,TTT

,T_TEMP

,WIN_LIST

簡單驗證一下，可以看到導出的資訊很“幹淨”，我們在資料導入的時候也可以做類似的操作。

[ora11g@rac1 dbm_lite]$ exp n1/n1 file=a.dmp parfile=a.par rows=n statistics=none

Export: Release 11.2.0.3.0 - Production on Tue Aug 26 07:34:19 2014

About to export specified tables via Conventional Path ...