作為DBA，經常需要在不同資料庫環境間做資料的匯入匯出，exp/imp就是這樣的輕便快捷的客戶端工具，可以很方便的在不同資料庫之間轉移資料物件，即使資料庫位於不同的硬體或者軟體平臺上。exp會轉儲產生對應的二進位制檔案，裡面包含資料的定義資訊，資料內容等，也就是我們通常所說的dump檔案。

exp/imp是比較經典的資料匯出匯入工具,不過自expdp/impdp推出以來，exp/imp還是受到了不少的冷落，在新的資料庫版本中，支援力度都集中在了expdp/impdp上面。Expdp/impdp是基於服務端的資料匯入匯出工具，效能上和功能上要更勝一籌，不過不管怎樣，exp/imp確實是一款比較輕巧的工具,對於小表的處理效果還是相當不錯的。

使用exp/imp的場景

使用exp/imp主要有4種模式：資料庫模式，表模式，使用者模式，可傳輸表空間模式，在此基礎上使用比較多的還有基於query選項的資料選擇性抽取和資料結構匯出。

資料庫模式

資料庫模式主要是作為全庫備份使用的，可以匯出除sys之外的資料庫裡的所有物件，在資料量較小的情況下是一個不錯的選擇。

exp n1/n1 file=db_backup.dmp full=y

表模式

表模式可以匯出某個使用者下指定的表，比如我們需要匯出的表名為：test1,test2

exp n1/n1 file=table_mode.dmp tables=test1,test2

使用者模式

使用者模式可以匯出指定使用者下所有的物件,比如匯出使用者user1下所有的物件

exp n1/n1 file=user_mode.dmp owner=user1

傳輸表空間模式常作為資料遷移時的一種方法，在資料遷移篇中會有詳細的描述海量資料遷移之傳輸表空間(一) http://blog.itpub.net/23718752/viewspace-1703358/

同理，imp中也會存在同樣的四種模式，使用方法都是類似的。

除了常用的4種模式之外，使用query選項選擇性匯出資料也是一種很使用的方法。比如存在一個表test,我們希望根據欄位object_type=’TABLE’的條件來選擇性匯出資料，可以使用query選項完成。

使用Query選項做選擇性資料匯出

exp n1/n1 file=query_bak.dmp query=\" where object_type= \'TABLE\' \" tables=test

得到物件的ddl語句

還有一個功能點可能大家不太注意，就是能夠很方便得到ddl相關的語句。

比如我們希望得到表test的建表語句，可以透過exp/imp這麼做。

exp n1/n1 rows=n compress=n tables=test file=test.dmp log=exp_test.log buffer=10240000

imp n1/n1 rows=n full=y ignore=y show=y file=test.dmp log=imp_test.log buffer=10240000

得到的建表語句就會是下面的樣子。

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing N1's objects into N1

"CREATE TABLE "TEST" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VA"

"RCHAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMB"

"ER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "C"

"REATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMEST"

"AMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATE"

"D" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER NOT NULL ENABLE"

", "EDITION_NAME" VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2"

"55 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GRO"

"UPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"

"CREATE INDEX "IDX_TEST" ON "TEST" ("OBJECT_ID" DESC , "OBJECT_NAME" , "OBJE"

"CT_TYPE" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1"

"048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"

"ESPACE "USERS" LOGGING"

看起來確實是不太直觀，如果自己想直接使用還得手工格式化，這個時候我們可以藉助awk來格式化一下。看起來好像挺抽象，但是功能還是很強大的。

awk '

/ \"BEGIN / { N=1; }

/ \"CREATE / { N=1; }

/ \"CREATE INDEX/ { N=1; }

/ \"CREATE UNIQUE INDEX/ { N=1; }

/ \"ALTER / { N=1; }

/ \" ALTER / { N=1; }

/ \"ANALYZE / { N=1; }

/ \"GRANT / { N=1; }

/ \"COMMENT / { N=1; }

/ \"AUDIT / { N=1; }

N==1 { printf "\n/\n"; N++ }

/\"$/ {

if (N==0) next;

s=index( $0, "\"" );

ln0=length( $0 )

if ( s!=0 ) {

lcnt++

if ( lcnt >= 30 ) {

ln=substr( $0,s+1,length( substr($0,s+1))-1)

t=index( ln, ")," )

if ( t==0 ) { t=index( ln, ", " ) }

if ( t==0 ) { t=index( ln, ") " ) }

if ( t > 0 ) {

printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2)

lcnt=0

}

else {

printf "%s", ln

if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }

}

else {

printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )

if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }

}

END { printf "\n/\n"}

' imp_test.log |sed '1,2d; /^$/ d;

s/STORAGE *(INI/~ STORAGE (INI/g;

s/, "/,~ "/g;

s/ (\"/~ &/g;

s/PCT[FI]/~ &/g;

s/[( ]PARTITION /~&/g;

s/) TABLESPACE/)~ TABLESPACE/g;

s/ , / ,~/g;

s/ DATAFILE /&~/' | tr "~" "\n"

看看格式化後的結果,如此顯著的結果是不是有一種馬上想試試的衝動。

CREATE TABLE "TEST"

("OWNER" VARCHAR2(30) NOT NULL ENABLE,

"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,

"SUBOBJECT_NAME" VARCHAR2(30),

"OBJECT_ID" NUMBER NOT NULL ENABLE,

"DATA_OBJECT_ID" NUMBER,

"OBJECT_TYPE" VARCHAR2(19),

"CREATED" DATE NOT NULL ENABLE,

"LAST_DDL_TIME" DATE NOT NULL ENABLE,

"TIMESTAMP" VARCHAR2(19),

"STATUS" VARCHAR2(7),

"TEMPORARY" VARCHAR2(1),

"GENERATED" VARCHAR2(1),

"SECONDARY" VARCHAR2(1),

"NAMESPACE" NUMBER NOT NULL ENABLE,

"EDITION_NAME" VARCHAR2(30))

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "USERS" LOGGING NOCOMPRESS

/

CREATE INDEX "IDX_TEST" ON "TEST"

("OBJECT_ID" DESC ,

"OBJECT_NAME" ,

"OBJECT_TYPE" )

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "USERS" LOGGING

/

淺談exp/imp(上)

使用exp/imp的場景

相關文章