ORACLE EXP/IMP的使用詳解

 　　匯入/匯出是ORACLE倖存的最古老的兩個命令列工具，其實我從來不認為Exp/Imp是一種好的備份方式，正確的說法是Exp/Imp只能是一個好的轉儲工具，特別是在小型資料庫的轉儲，資料表空間的遷移，表的抽取，檢測邏輯和物理衝突等中有不小的功勞。當然，我們也可以把它作為小型資料庫的物理備份後的一個邏輯輔助備份，也是不錯的建議。對于越來越大的資料庫，特別是TB級資料庫和越來越多資料倉儲的出現，EXP/IMP越來越力不從心了，這個時候，資料庫的備份都轉向了RMAN和第三方工具。下面說明一下EXP/IMP的使用。
　　如何使exp的協助以不同的字元集顯示：set nls_lang=simplified chinese_china.zhs16gbk，通過設定環境變數，可以讓exp的協助以中文顯示，如果set nls_lang=American_america.字元集，那麼協助就是英文的了EXP的所有參數（括弧中為參數的預設值）：
USERID       使用者名稱/口令     如： USERID=duanl/duanl      
FULL         匯出整個資料庫 (N)
BUFFER       資料緩衝區的大小         
OWNER       所有者使用者名稱列表,你希望匯出哪個使用者的對象，就用owner=username
FILE          輸出檔案 (EXPDAT.DMP)    
TABLES       表名列表 ,指定匯出的table名稱，如：TABLES=table1,table2
COMPRESS    匯入一個extent (Y)   
RECORDLENGTH  IO 記錄的長度
GRANTS       匯出許可權 (Y)            
INCTYPE       增量匯出類型
INDEXES       匯出索引 (Y)           
RECORD       跟蹤增量匯出 (Y)
ROWS          匯出資料行 (Y)        
PARFILE       參數檔案名稱,如果你exp的參數很多，可以存成參數檔案.
CONSTRAINTS  匯出約束 (Y)    
CONSISTENT   交叉表一致性
LOG           螢幕輸出的記錄檔    
STATISTICS    分析對象 (ESTIMATE)
DIRECT        直接路徑 (N)              
TRIGGERS     匯出觸發器 (Y)
FEEDBACK     顯示每 x 行 (0) 的進度
FILESIZE       各轉儲檔案的最大尺寸
QUERY         選定匯出表子集的子句
下列關鍵字僅用於可傳輸的資料表空間
TRANSPORT_TABLESPACE 匯出可傳輸的資料表空間中繼資料 (N)
TABLESPACES 將傳輸的資料表空間列表

IMP的所有參數（括弧中為參數的預設值）：
USERID   使用者名稱/口令           
FULL     匯入整個檔案 (N)
BUFFER   資料緩衝區大小         
FROMUSER     所有人使用者名稱列表
FILE     輸入檔案 (EXPDAT.DMP)    
TOUSER       使用者名稱列表
SHOW     只列出檔案內容 (N)
TABLES      表名列表
IGNORE   忽略建立錯誤 (N)    
RECORDLENGTH  IO 記錄的長度
GRANTS  匯入許可權 (Y)          
INCTYPE      增量匯入類型
INDEXES 匯入索引 (Y)          
COMMIT       提交數組插入 (N)
ROWS    匯入資料行 (Y)       
PARFILE      參數檔案名稱
LOG      螢幕輸出的記錄檔   
CONSTRAINTS  匯入限制 (Y)
DESTROY  覆蓋資料表空間資料檔案 (N)
INDEXFILE 將表/索引資訊寫入指定的檔案
SKIP_UNUSABLE_INDEXES  跳過不可用索引的維護 (N)
ANALYZE  執行轉儲檔案中的 ANALYZE 語句 (Y)
FEEDBACK 顯示每 x 行 (0) 的進度
TOID_NOVALIDATE  跳過指定類型 id 的校正
FILESIZE 各轉儲檔案的最大尺寸
RECALCULATE_STATISTICS 重新計算統計值 (N)
下列關鍵字僅用於可傳輸的資料表空間
TRANSPORT_TABLESPACE 匯入可傳輸的資料表空間中繼資料 (N)
TABLESPACES 將要傳輸到資料庫的資料表空間
DATAFILES 將要傳輸到資料庫的資料檔案
TTS_OWNERS 擁有可傳輸資料表空間集中資料的使用者

關於增量參數的說明：exp/imp的增量並不是真正意義上的增量，所以最好不要使用。

使用方法：
Exp parameter_name=value or Exp parameter_name=(value1,value2……)
只要輸入參數help=y就可以看到所有協助.

EXP常用選項
1.FULL，這個用於匯出整個資料庫，在ROWS=N一起使用時，可以匯出整個資料庫的結構。例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y
2. OWNER和TABLE，這兩個選項用於定義EXP的對象。OWNER定義匯出指定使用者的對象；TABLE指定EXP的table名稱，例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log owner=duanl
exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap
3.BUFFER和FEEDBACK，在匯出比較多的資料時，我會考慮設定這兩個參數。例如：
exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT
4.FILE和LOG，這兩個參數分別指定備份的DMP名稱和LOG名稱，包括檔案名稱和目錄，例子見上面。
5.COMPRESS參數不壓縮匯出資料的內容。用來控制匯出對象的storage語句如何產生。預設值為Y，使用預設值，對象的儲存語句的init extent等於當前置出對象的extent的總和。推薦使用COMPRESS＝N。
6. FILESIZE該選項在8i中可用。如果匯出的dmp檔案過大時，最好使用FILESIZE參數，限制檔案大小不要超過2G。如：
exp userid=duanl/duanl file=f1,f2,f3,f4,f5 filesize=2G owner=scott
這樣將建立f1.dmp, f2.dmp等一系列檔案，每個大小都為2G，如果匯出的總量小於10G
EXP不必建立f5.bmp.

IMP常用選項
1、FROMUSER和TOUSER，使用它們實現將資料從一個SCHEMA中匯入到另外一個SCHEMA中。例如：假設我們做exp時匯出的為test的對象,現在我們想把對象匯入使用者：
imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1
2、IGNORE、GRANTS和INDEXES，其中IGNORE參數將忽略表的存在，繼續匯入，這個對於需要調整表的儲存參數時很有用，我們可以先根據實際情況用合理的儲存參數建好表，然後直接匯入資料。而GRANTS和INDEXES則表示是否匯入授權和索引，如果想使用新的儲存參數重建索引，或者為了加快到入速度，我們可以考慮將INDEXES設為N，而GRANTS一般都是Y。例如：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N

資料表空間傳輸
    資料表空間傳輸是8i新增加的一種快速在資料庫間移動資料的一種辦法，是把一個資料庫上的格式資料檔案附加到另外一個資料庫中，而不是把資料匯出成Dmp檔案，這在有些時候是非常管用的，因為傳輸資料表空間移動資料就象複製檔案一樣快。
關於傳輸資料表空間有一些規則，即：
·來源資料庫和目標資料庫必須運行在相同的硬體平台上。
·來源資料庫與目標資料庫必須使用相同的字元集。
·來源資料庫與目標資料庫一定要有相同大小的資料區塊
·目標資料庫不能有與遷移資料表空間同名的資料表空間
·SYS的對象不能遷移
·必須傳輸自包含的對象集
·有一些對象，如物化視圖，基於函數的索引等不能被傳輸
可以用以下的方法來檢測一個資料表空間或一套資料表空間是否符合傳輸通訊協定：
exec sys.dbms_tts.transport_set_check(‘tablespace_name’,true);
select * from sys.transport_set_violation;
如果沒有行選擇，表示該資料表空間只包含表資料，並且是自包含的。對於有些非自包含的資料表空間，如資料資料表空間和索引資料表空間，可以一起傳輸。
以下為簡要使用步驟，如果想參考詳細使用方法，也可以參考ORACLE線上說明。
1.設定資料表空間為唯讀（假定資料表空間名字為APP_Data 和APP_Index）
alter tablespace app_data read only;
alter tablespace app_index read only;
2.發出EXP命令
SQL>host exp userid=”””sys/password as sysdba””” 
transport_tablespace=y tablespace=(app_data, app_index)
以上需要注意的是
·為了在SQL中執行EXP，USERID必須用三個引號，在UNIX中也必須注意避免“/”的使用
·在816和以後，必須使用sysdba才能操作
·這個命令在SQL中必須放置在一行（這裡是因為顯示問題放在了兩行）
3.拷貝資料檔案到另一個地點，即目標資料庫
  可以是cp(unix)或copy(windows)或通過ftp傳輸檔案（一定要在bin方式）
4.把本地的資料表空間設定為讀寫
5.在目標資料庫附加該資料檔案
imp file=expdat.dmp userid=”””sys/password as sysdba””” transport_tablespace=y “datafile=(c:\temp\app_data,c:\temp\app_index)”
6.設定目標資料庫資料表空間為讀寫
alter tablespace app_data read write;
alter tablespace app_index read write;

最佳化EXP/IMP的方法：
    當需要exp/imp的資料量比較大時，這個過程需要的時間是比較長的，我們可以用一些方法來最佳化exp/imp的操作。
exp:使用直接路徑 direct=y
oracle會避開sql語句處理引擎,直接從資料庫檔案中讀取資料,然後寫入匯出檔案.
可以在匯出日誌中觀察到: exp-00067: table xxx will be exported in conventional path
    如果沒有使用直接路徑,必須保證buffer參數的值足夠大.
    有一些參數於direct=y不相容,無法用直接路徑匯出可移動的tablespace,或者用query參數匯出資料庫子集.
    當匯入匯出的資料庫運行在不同的os下時,必須保證recordlength參數的值一致.

imp:通過以下幾個途徑最佳化
1.避免磁碟排序
將sort_area_size設定為一個較大的值,比如100M
2.避免日誌切換等待
增加重做日誌組的數量,增大記錄檔大小.
3.最佳化日誌緩衝區
比如將log_buffer容量擴大10倍(最大不要超過5M)
4.使用陣列插入與提交
commit = y
注意:陣列方式不能處理包含LOB和LONG類型的表,對於這樣的table,如果使用commit = y,每插入一行,就會執行一次提交.
5.使用NOLOGGING方式減小重做日誌大小
在匯入時指定參數indexes=n,只匯入資料而忽略index,在導完資料後在通過指令碼建立index,指定 NOLOGGING選項

匯出/匯入與字元集
    進行資料的匯入匯出時，我們要注意關於字元集的問題。在EXP/IMP過程中我們需要注意四個字元集的參數：匯出端的用戶端字元集，匯出端資料庫字元集，匯入端的用戶端字元集，匯入端資料庫字元集。
我們首先需要查看這四個字元集參數。
查看資料庫的字元集的資訊：
SQL> select * from nls_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是當前資料庫的字元集。

我們再來查看用戶端的字元集資訊：
用戶端字元集的參數NLS_LANG=_< territory >.
language：指定oracle訊息使用的語言，日期中日和月的顯示。
Territory：指定貨幣和數位格式，地區和計算星期及日期的習慣。
Characterset：控制用戶端應用程式使用的字元集。通常設定或等於用戶端的字碼頁。或者對於unicode應用設為UTF8。
在windows中，查詢和修改NLS_LANG可在註冊表中進行：
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOMExx\
xx指存在多個Oracle_HOME時的系統編號。

在unix中：
$ env|grep NLS_LANG
NLS_LANG=simplified chinese_china.ZHS16GBK

修改可用：
$ export NLS_LANG=AMERICAN_AMERICA.UTF8

通常在匯出時最好把用戶端字元集設定得和資料庫端相同。當進行資料匯入時，主要有以下兩種情況：
(1)    來源資料庫和目標資料庫具有相同的字元集設定。
這時，只需設定匯出和匯入端的用戶端NLS_LANG等於資料庫字元集即可。
(2)    來源資料庫和目標資料庫字元集不同。
    先將匯出端用戶端的NLS_LANG設定成和匯出端的資料庫字元集一致，匯出資料，然後將匯入端用戶端的NLS_LANG設定成和匯出端一致，匯入資料，這樣轉換隻發生在資料庫端，而且只發生一次。
    這種情況下，只有當匯入端資料庫字元集為匯出端資料庫字元集的嚴格超集時，資料才能完全導成功，否則，可能會有資料不一致或亂碼出現。

不同版本的EXP/IMP問題
    一般來說，從低版本匯入到高版本問題不大，麻煩的是將高版本的資料匯入到低版本中，在Oracle9i之前，不同版本Oracle之間的EXP/IMP可以通過下面的方法來解決：
1、在高版本資料庫上運行底版本的catexp.sql；
2、使用低版本的EXP來匯出高版本的資料；
3、使用低版本的IMP將資料庫匯入到低版本資料庫中；
4、在高版本資料庫上重新運行高版本的catexp.sql指令碼。
但在9i中，上面的方法並不能解決問題。如果直接使用低版本EXP/IMP會出現如下錯誤：
EXP-00008: orACLE error %lu encountered
orA-00904: invalid column name
這已經是一個公布的BUG，需要等到Oracle10.0才能解決，BUG號為2261722，你可以到METALINK上去查看有關此BUG的詳細資料。
BUG歸BUG，我們的工作還是要做，在沒有Oracle的支援之前，我們就自己解決。在Oracle9i中執行下面的SQL重建exu81rls視圖即可。
Create or REPLACE 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 where role='Select_CATALOG_ROLE') 
) 
/ 
grant select on sys.exu81rls to public; 
/ 

可以跨版本的使用EXP/IMP，但必須正確地使用EXP和IMP的版本： 
1、總是使用IMP的版本匹配資料庫的版本，如：要匯入到817中，使用817的IMP工具。
2、總是使用EXP的版本匹配兩個資料庫中最低的版本，如：從9201往817中匯入，則使用817版本的EXP工具。

本文出自 51CTO.COM技術部落格