oracle exp/imp 詳解

導(dǎo)入/導(dǎo)出是ORACLE幸存的最古老的兩個(gè)命令行工具，其實(shí)我從來不認(rèn)為Exp/Imp是一種好的備份方式，正確的說法是Exp/Imp只能是一個(gè)好的轉(zhuǎn)儲(chǔ)工具，特別是在小型數(shù)據(jù)庫的轉(zhuǎn)儲(chǔ)，表空間的遷移，表的抽取，檢測(cè)邏輯和物理沖突等中有不小的功勞。當(dāng)然，我們也可以把它作為小型數(shù)據(jù)庫的物理備份后的一個(gè)邏輯輔助備份，也是不錯(cuò)的建議。對(duì)于越來越大的數(shù)據(jù)庫，特別是TB級(jí)數(shù)據(jù)庫和越來越多數(shù)據(jù)倉(cāng)庫的出現(xiàn)，EXP/IMP越來越力不從心了，這個(gè)時(shí)候，數(shù)據(jù)庫的備份都轉(zhuǎn)向了RMAN和第三方工具。下面說明一下EXP/IMP的使用。

　　如何使exp的幫助以不同的字符集顯示：set nls_lang=simplified chinese_china.zhs16gbk，通過設(shè)置環(huán)境變量，可以讓exp的幫助以中文顯示，如果setnls_lang=American_america.字符集，那么幫助就是英文的了

EXP的所有參數(shù)（括號(hào)中為參數(shù)的默認(rèn)值）：

USERID        用戶名/口令      如： USERID=duanl/duanl     

FULL          導(dǎo)出整個(gè)數(shù)據(jù)庫 (N)

BUFFER        數(shù)據(jù)緩沖區(qū)的大小        

OWNER       所有者用戶名列表,你希望導(dǎo)出哪個(gè)用戶的對(duì)象，就用owner=username

FILE           輸出文件 (EXPDAT.DMP)   

TABLES        表名列表 ,指定導(dǎo)出的table名稱，如：TABLES=table1,table2

COMPRESS    導(dǎo)入一個(gè)extent (Y)  

RECORDLENGTH   IO 記錄的長(zhǎng)度

GRANTS        導(dǎo)出權(quán)限 (Y)           

INCTYPE        增量導(dǎo)出類型

INDEXES        導(dǎo)出索引 (Y)          

RECORD        跟蹤增量導(dǎo)出 (Y)

ROWS           導(dǎo)出數(shù)據(jù)行 (Y)       

PARFILE        參數(shù)文件名,如果你exp的參數(shù)很多，可以存成參數(shù)文件.

CONSTRAINTS   導(dǎo)出約束 (Y)   

CONSISTENT    交叉表一致性

LOG            屏幕輸出的日志文件   

STATISTICS     分析對(duì)象 (ESTIMATE)

DIRECT         直接路徑 (N)             

TRIGGERS      導(dǎo)出觸發(fā)器 (Y)

FEEDBACK      顯示每 x 行 (0) 的進(jìn)度

FILESIZE        各轉(zhuǎn)儲(chǔ)文件的最大尺寸

QUERY          選定導(dǎo)出表子集的子句

下列關(guān)鍵字僅用于可傳輸?shù)谋砜臻g

TRANSPORT_TABLESPACE 導(dǎo)出可傳輸?shù)谋砜臻g元數(shù)據(jù) (N)

TABLESPACES 將傳輸?shù)谋砜臻g列表

 程序代碼

IMP的所有參數(shù)（括號(hào)中為參數(shù)的默認(rèn)值）：

USERID   用戶名/口令          

FULL     導(dǎo)入整個(gè)文件 (N)

BUFFER   數(shù)據(jù)緩沖區(qū)大小        

FROMUSER      所有人用戶名列表

FILE     輸入文件 (EXPDAT.DMP)   

TOUSER        用戶名列表

SHOW     只列出文件內(nèi)容 (N)

TABLES      表名列表

IGNORE   忽略創(chuàng)建錯(cuò)誤 (N)   

RECORDLENGTH   IO 記錄的長(zhǎng)度

GRANTS  導(dǎo)入權(quán)限 (Y)         

INCTYPE       增量導(dǎo)入類型

INDEXES 導(dǎo)入索引 (Y)         

COMMIT        提交數(shù)組插入 (N)

ROWS    導(dǎo)入數(shù)據(jù)行 (Y)      

PARFILE       參數(shù)文件名

LOG      屏幕輸出的日志文件  

CONSTRAINTS   導(dǎo)入限制 (Y)

DESTROY  覆蓋表空間數(shù)據(jù)文件 (N)

INDEXFILE 將表/索引信息寫入指定的文件

SKIP_UNUSABLE_INDEXES   跳過不可用索引的維護(hù) (N)

ANALYZE  執(zhí)行轉(zhuǎn)儲(chǔ)文件中的 ANALYZE 語句 (Y)

FEEDBACK 顯示每 x 行 (0) 的進(jìn)度

TOID_NOVALIDATE   跳過指定類型 id 的校驗(yàn)

FILESIZE 各轉(zhuǎn)儲(chǔ)文件的最大尺寸

RECALCULATE_STATISTICS 重新計(jì)算統(tǒng)計(jì)值 (N)

下列關(guān)鍵字僅用于可傳輸?shù)谋砜臻g

TRANSPORT_TABLESPACE 導(dǎo)入可傳輸?shù)谋砜臻g元數(shù)據(jù) (N)

TABLESPACES 將要傳輸?shù)綌?shù)據(jù)庫的表空間

DATAFILES 將要傳輸?shù)綌?shù)據(jù)庫的數(shù)據(jù)文件

TTS_OWNERS 擁有可傳輸表空間集中數(shù)據(jù)的用戶

關(guān)于增量參數(shù)的說明：exp/imp的增量并不是真正意義上的增量，所以最好不要使用。

使用方法：

Exp parameter_name=value or Expparameter_name=(value1,value2……)

只要輸入?yún)?shù)help=y就可以看到所有幫助.

EXP常用選項(xiàng)

1.FULL，這個(gè)用于導(dǎo)出整個(gè)數(shù)據(jù)庫，在ROWS=N一起使用時(shí)，可以導(dǎo)出整個(gè)數(shù)據(jù)庫的結(jié)構(gòu)。例如：

exp userid=test/test file=./db_str.dmplog=./db_str.log full=y rows=n compress=y direct=y

2. OWNER和TABLE，這兩個(gè)選項(xiàng)用于定義EXP的對(duì)象。OWNER定義導(dǎo)出指定用戶的對(duì)象；TABLE指定EXP的table名稱，例如：

exp userid=test/test file=./db_str.dmplog=./db_str.log owner=duanl

exp userid=test/test file=./db_str.dmp log=./db_str.logtable=nc_data,fi_arap

3.BUFFER和FEEDBACK，在導(dǎo)出比較多的數(shù)據(jù)時(shí)，我會(huì)考慮設(shè)置這兩個(gè)參數(shù)。例如：

exp userid=test/test file=yw97_2003.dmplog=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT

4.FILE和LOG，這兩個(gè)參數(shù)分別指定備份的DMP名稱和LOG名稱，包括文件名和目錄，例子見上面。

5.COMPRESS參數(shù)不壓縮導(dǎo)出數(shù)據(jù)的內(nèi)容。用來控制導(dǎo)出對(duì)象的storage語句如何產(chǎn)生。默認(rèn)值為Y，使用默認(rèn)值，對(duì)象的存儲(chǔ)語句的init extent等于當(dāng)前導(dǎo)出對(duì)象的extent的總和。推薦使用COMPRESS＝N。

6. FILESIZE該選項(xiàng)在8i中可用。如果導(dǎo)出的dmp文件過大時(shí)，最好使用FILESIZE參數(shù)，限制文件大小不要超過2G。如：

exp userid=duanl/duanl file=f1,f2,f3,f4,f5filesize=2G owner=scott

這樣將創(chuàng)建f1.dmp, f2.dmp等一系列文件，每個(gè)大小都為2G，如果導(dǎo)出的總量小于10G

EXP不必創(chuàng)建f5.bmp.

IMP常用選項(xiàng)DBA

1、FROMUSER和TOUSER，使用它們實(shí)現(xiàn)將數(shù)據(jù)從一個(gè)SCHEMA中導(dǎo)入到另外一個(gè)SCHEMA中。例如：假設(shè)我們做exp時(shí)導(dǎo)出的為test的對(duì)象,現(xiàn)在我們想把對(duì)象導(dǎo)入用戶：

imp userid=test1/test1 file=expdat.dmpfromuser=test1 touser=test1

2、IGNORE、GRANTS和INDEXES，其中IGNORE參數(shù)將忽略表的存在，繼續(xù)導(dǎo)入，這個(gè)對(duì)于需要調(diào)整表的存儲(chǔ)參數(shù)時(shí)很有用，我們可以先根據(jù)實(shí)際情況用合理的存儲(chǔ)參數(shù)建好表，然后直接導(dǎo)入數(shù)據(jù)。而GRANTS和INDEXES則表示是否導(dǎo)入授權(quán)和索引，如果想使用新的存儲(chǔ)參數(shù)重建索引，或者為了加快到入速度，我們可以考慮將INDEXES設(shè)為N，而GRANTS一般都是Y。例如：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1indexes=N

表空間傳輸

     表空間傳輸是8i新增加的一種快速在數(shù)據(jù)庫間移動(dòng)數(shù)據(jù)的一種辦法，是把一個(gè)數(shù)據(jù)庫上的格式數(shù)據(jù)文件附加到另外一個(gè)數(shù)據(jù)庫中，而不是把數(shù)據(jù)導(dǎo)出成Dmp文件，這在有些時(shí)候是非常管用的，因?yàn)閭鬏敱砜臻g移動(dòng)數(shù)據(jù)就象復(fù)制文件一樣快。

關(guān)于傳輸表空間有一些規(guī)則，即：

源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫必須運(yùn)行在相同的硬件平臺(tái)上。

源數(shù)據(jù)庫與目標(biāo)數(shù)據(jù)庫必須使用相同的字符集。

源數(shù)據(jù)庫與目標(biāo)數(shù)據(jù)庫一定要有相同大小的數(shù)據(jù)塊

目標(biāo)數(shù)據(jù)庫不能有與遷移表空間同名的表空間

SYS的對(duì)象不能遷移

必須傳輸自包含的對(duì)象集

有一些對(duì)象，如物化視圖，基于函數(shù)的索引等不能被傳輸

可以用以下的方法來檢測(cè)一個(gè)表空間或一套表空間是否符合傳輸標(biāo)準(zhǔn)：

execsys.dbms_tts.transport_set_check(‘tablespace_name’,true);

select * from sys.transport_set_violation;

如果沒有行選擇，表示該表空間只包含表數(shù)據(jù)，并且是自包含的。對(duì)于有些非自包含的表空間，如數(shù)據(jù)表空間和索引表空間，可以一起傳輸。

以下為簡(jiǎn)要使用步驟，如果想?yún)⒖荚敿?xì)使用方法，也可以參考ORACLE聯(lián)機(jī)幫助。

1.設(shè)置表空間為只讀（假定表空間名字為APP_Data 和APP_Index）

alter tablespace app_data read only;

alter tablespace app_index read only;

2.發(fā)出EXP命令

SQL>host exp userid=”””sys/password assysdba”””

transport_tablespace=ytablespace=(app_data, app_index)

以上需要注意的是

為了在SQL中執(zhí)行EXP，USERID必須用三個(gè)引號(hào)，在UNIX中也必須注意避免“/”的使用

在816和以后，必須使用sysdba才能操作

這個(gè)命令在SQL中必須放置在一行（這里是因?yàn)轱@示問題放在了兩行）

3.拷貝數(shù)據(jù)文件到另一個(gè)地點(diǎn)，即目標(biāo)數(shù)據(jù)庫

   可以是cp(unix)或copy(windows)或通過ftp傳輸文件（一定要在bin方式）

4.把本地的表空間設(shè)置為讀寫

5.在目標(biāo)數(shù)據(jù)庫附加該數(shù)據(jù)文件

imp file=expdat.dmp userid=”””sys/passwordas sysdba””” transport_tablespace=y “datafile=(c:\temp\app_data,c:\temp\app_index)”

6.設(shè)置目標(biāo)數(shù)據(jù)庫表空間為讀寫

alter tablespace app_data read write;

alter tablespace app_index read write;

優(yōu)化EXP/IMP的方法：

     當(dāng)需要exp/imp的數(shù)據(jù)量比較大時(shí)，這個(gè)過程需要的時(shí)間是比較長(zhǎng)的，我們可以用一些方法來優(yōu)化exp/imp的操作。

exp:使用直接路徑 direct=y

oracle會(huì)避開sql語句處理引擎,直接從數(shù)據(jù)庫文件中讀取數(shù)據(jù),然后寫入導(dǎo)出文件.

可以在導(dǎo)出日志中觀察到: exp-00067:table xxx will be exported in conventional path

     如果沒有使用直接路徑,必須保證buffer參數(shù)的值足夠大.

     有一些參數(shù)于direct=y不兼容,無法用直接路徑導(dǎo)出可移動(dòng)的tablespace,或者用query參數(shù)導(dǎo)出數(shù)據(jù)庫子集.

     當(dāng)導(dǎo)入導(dǎo)出的數(shù)據(jù)庫運(yùn)行在不同的os下時(shí),必須保證recordlength參數(shù)的值一致.

imp:通過以下幾個(gè)途徑優(yōu)化

1.避免磁盤排序

將sort_area_size設(shè)置為一個(gè)較大的值,比如100M

2.避免日志切換等待

增加重做日志組的數(shù)量,增大日志文件大小.

3.優(yōu)化日志緩沖區(qū)

比如將log_buffer容量擴(kuò)大10倍(最大不要超過5M)

4.使用陣列插入與提交

commit = y

注意:陣列方式不能處理包含LOB和LONG類型的表,對(duì)于這樣的table,如果使用commit = y,每插入一行,就會(huì)執(zhí)行一次提交.

5.使用NOLOGGING方式減小重做日志大小

在導(dǎo)入時(shí)指定參數(shù)indexes=n,只導(dǎo)入數(shù)據(jù)而忽略index,在導(dǎo)完數(shù)據(jù)后在通過腳本創(chuàng)建index,指定 NOLOGGING選項(xiàng)

導(dǎo)出/導(dǎo)入與字符集

     進(jìn)行數(shù)據(jù)的導(dǎo)入導(dǎo)出時(shí)，我們要注意關(guān)于字符集的問題。在EXP/IMP過程中我們需要注意四個(gè)字符集的參數(shù)：導(dǎo)出端的客戶端字符集，導(dǎo)出端數(shù)據(jù)庫字符集，導(dǎo)入端的客戶端字符集，導(dǎo)入端數(shù)據(jù)庫字符集。

我們首先需要查看這四個(gè)字符集參數(shù)。

查看數(shù)據(jù)庫的字符集的信息：

SQL> select * fromnls_database_parameters;

PARAMETER                       VALUE

--------------------------------------------------------------------------------------------------------------

NLS_LANGUAGE                    AMERICAN

NLS_TERRITORY                    AMERICA

NLS_CURRENCY                    $

NLS_ISO_CURRENCY                AMERICA

NLS_NUMERIC_CHARACTERS          .,

NLS_CHARACTERSET                ZHS16GBK

NLS_CALENDAR                    GREGORIAN

NLS_DATE_FORMAT                 DD-MON-RR

NLS_DATE_LANGUAGE               AMERICAN

NLS_SORT                          BINARY

NLS_TIME_FORMAT                 HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT            DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT              HH.MI.SSXFF AM TZH:TZM

NLS_TIMESTAMP_TZ_FORMAT         DD-MON-RR HH.MI.SSXFF AM TZH:TZM

NLS_DUAL_CURRENCY               $

NLS_COMP                        BINARY

NLS_NCHAR_CHARACTERSET          ZHS16GBK

NLS_RDBMS_VERSION               8.1.7.4.1

NLS_CHARACTERSET：ZHS16GBK是當(dāng)前數(shù)據(jù)庫的字符集。

我們?cè)賮聿榭纯蛻舳说淖址畔ⅲ?/span>

客戶端字符集的參數(shù)NLS_LANG=_<territory >.

language：指定oracle消息使用的語言，日期中日和月的顯示。

Territory：指定貨幣和數(shù)字的格式，地區(qū)和計(jì)算星期及日期的習(xí)慣。

Characterset：控制客戶端應(yīng)用程序使用的字符集。通常設(shè)置或等于客戶端的代碼頁?；蛘邔?duì)于unicode應(yīng)用設(shè)為UTF8。

在windows中，查詢和修改NLS_LANG可在注冊(cè)表中進(jìn)行：

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOMExx\

xx指存在多個(gè)Oracle_HOME時(shí)的系統(tǒng)編號(hào)。

在unix中：

$ env|grep NLS_LANG

NLS_LANG=simplified chinese_china.ZHS16GBK

修改可用：

$ export NLS_LANG=AMERICAN_AMERICA.UTF8

通常在導(dǎo)出時(shí)最好把客戶端字符集設(shè)置得和數(shù)據(jù)庫端相同。當(dāng)進(jìn)行數(shù)據(jù)導(dǎo)入時(shí)，主要有以下兩種情況：

(1)    源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫具有相同的字符集設(shè)置。

這時(shí)，只需設(shè)置導(dǎo)出和導(dǎo)入端的客戶端NLS_LANG等于數(shù)據(jù)庫字符集即可。

(2)    源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫字符集不同。

     先將導(dǎo)出端客戶端的NLS_LANG設(shè)置成和導(dǎo)出端的數(shù)據(jù)庫字符集一致，導(dǎo)出數(shù)據(jù)，然后將導(dǎo)入端客戶端的NLS_LANG設(shè)置成和導(dǎo)出端一致，導(dǎo)入數(shù)據(jù)，這樣轉(zhuǎn)換只發(fā)生在數(shù)據(jù)庫端，而且只發(fā)生一次。

     這種情況下，只有當(dāng)導(dǎo)入端數(shù)據(jù)庫字符集為導(dǎo)出端數(shù)據(jù)庫字符集的嚴(yán)格超集時(shí)，數(shù)據(jù)才能完全導(dǎo)成功，否則，可能會(huì)有數(shù)據(jù)不一致或亂碼出現(xiàn)。

不同版本的EXP/IMP問題

     一般來說，從低版本導(dǎo)入到高版本問題不大，麻煩的是將高版本的數(shù)據(jù)導(dǎo)入到低版本中，在Oracle9i之前，不同版本Oracle之間的EXP/IMP可以通過下面的方法來解決：

1、在高版本數(shù)據(jù)庫上運(yùn)行底版本的catexp.sql；

2、使用低版本的EXP來導(dǎo)出高版本的數(shù)據(jù)；

3、使用低版本的IMP將數(shù)據(jù)庫導(dǎo)入到低版本數(shù)據(jù)庫中；

4、在高版本數(shù)據(jù)庫上重新運(yùn)行高版本的catexp.sql腳本。

但在9i中，上面的方法并不能解決問題。如果直接使用低版本EXP/IMP會(huì)出現(xiàn)如下錯(cuò)誤：

EXP-00008: orACLE error %lu encountered

orA-00904: invalid column name

這已經(jīng)是一個(gè)公布的BUG，需要等到Oracle10.0才能解決，BUG號(hào)為2261722，你可以到METALINK上去查看有關(guān)此BUG的詳細(xì)信息。

BUG歸BUG，我們的工作還是要做，在沒有Oracle的支持之前，我們就自己解決。在Oracle9i中執(zhí)行下面的SQL重建exu81rls視圖即可。

Create orREPLACE view exu81rls

(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)

AS select u.name, o.name, r.pname,r.pfschma, r.ppname, r.pfname,

decode(bitand(r.stmt_type,1), 0,'','Select,')

|| decode(bitand(r.stmt_type,2), 0,'','Insert,')

|| decode(bitand(r.stmt_type,4), 0,'','Update,')

|| decode(bitand(r.stmt_type,8), 0,'','Delete,'),

r.check_opt, r.enable_flag,

DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)

from user$ u, obj$ o, rls$ r

where u.user# = o.owner#

and r.obj# = o.obj#

and (uid = 0 or

uid = o.owner# or

exists ( select * from session_roles whererole='Select_CATALOG_ROLE')

)

/

grant select on sys.exu81rls to public;

/

可以跨版本的使用EXP/IMP，但必須正確地使用EXP和IMP的版本：

1、總是使用IMP的版本匹配數(shù)據(jù)庫的版本，如：要導(dǎo)入到817中，使用817的IMP工具。

2、總是使用EXP的版本匹配兩個(gè)數(shù)據(jù)庫中最低的版本，如：從9201往817中導(dǎo)入，則使用817版本的EXP工具。