通過shell解析dump生成parfile

jeanron100發表於2014-08-26
原文網址 : http://blog.itpub.net/23718752/viewspace-1258482/
當我們得到一個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
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 tables via Conventional Path ...
. . exporting table                     BIG_INSERT
. . exporting table                      CLOB_TEST
. . 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                              T
. . exporting table                             T1
. . exporting table                             T2
. . exporting table                      TEMP_TEST
. . exporting table                           TEST
. . exporting table                      TEST_DATA
. . exporting table                             TT
. . exporting table                            TTT
. . exporting table                         T_TEMP
. . exporting table                       WIN_LIST
Export terminated successfully without warnings.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1258482/，如需轉載，請註明出處，否則將追究法律責任。

相關文章