Oracle exp/imp資料匯入匯出工具基本用法

標籤：

一、擷取協助

exp/imp help=y

二、資料匯出

1.將資料庫完全匯出，設定full選項
exp system/[email protected] file=d:\db.dmp full=y

2、匯出資料庫結構，不匯出資料，設定rows選項
exp system/[email protected] file=d:\db.dmp rows=n full=y

3、當匯出資料量較大時，可以分成多個檔案匯出，設定filesize選項
exp system/[email protected] file=d:\db1.dmp,d:\db2.dmp filesize=50M full=y

4.將資料庫中system使用者與sys使用者的表匯出，設定owner選項
exp system/[email protected] file=d:\Test_bak.dmp owner=(system,sys)

5.將資料庫中的表t_result,t_khtime匯出，設定tables選項
exp system/[email protected] file= d:\Test_bak.dmp tables=(t_result,t_khtime)

6、將資料庫中的表T_SCORE_RESULT中updatedate日期欄位大於某個值的資料匯出，設定query選項
exp kpuser/[email protected] file=d:\Test_bak.dmp tables=(T_SCORE_RESULT) query=\" where updatedate>to_date(‘2016-9-1 18:32:00‘,‘yyyy-mm-dd hh24:mi:ss‘)\"

三、資料匯入

1、匯入dmp檔案，如果表已經存在，會報錯且不匯入已經存在的表，設定ignore選項
imp system/[email protected] file=d:\Test_bak.dmp ignore=y

2、匯入dmp檔案中部分指定的表，設定tables選項
imp kpuser/[email protected]=d:\kpuser.dmp tables=(T_SCORE_RESULT)

3、匯入一個或一組指定使用者所屬的全部表、索引和其他對象，設定fromuser選項
imp system/[email protected] file=d:\kpuser.dmp fromuser=kpuser //kpuser必須存在
imp system/[email protected] file=d:\users.dmp fromuser=(kpuser,kpuser1,test) //kpuser,kpuser1,test使用者必須存在

4、將資料匯入指定的一個或多個使用者，設定fromuser和touser選項
imp system/manager file=d:\kpuser.dmp fromuser=kpuser touser=kpuser1 //kpuser1必須存在
imp system/manager file=d:\users.dmp fromuser=(kpuser,kpuser1) touser=(kpuser2, kpuser3) //kpuser2、kpuser3必須存在

四、exp/imp最佳化

當需要匯入/匯出的資料量比較大時,這個過程需要的時間是比較長的,我們可以用一些方法來最佳化exp/imp的操作。
1、exp
1）使用直接路徑 direct=y，oracle會避開sql語句處理引擎,直接從資料庫檔案中讀取資料,然後寫入匯出檔案
2）如果沒有使用直接路徑,必須保證buffer參數的值足夠大.

2、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選項

五、注意

1、源使用者(fromuser)是dmp檔案中表所屬的使用者，不是指當前資料庫中的使用者；目標使用者(touser)是指資料要匯入的使用者，在當前資料庫中必須不存在，如果不存在，需要先建立該使用者；如果只指定了fromuser，未指定touser，則預設匯入資料庫中和fromuser同名的使用者下，即fromuser指定的使用者必須在資料庫中存在。

2、如果匯入整個dmp檔案，即full=y，則最好把資料庫中dmp中對應的表都刪除再匯入

3、ignore選項
Oracle在恢複資料的過程中,當恢複某個表時,該表已經存在,就要根據ignore選項的設定來決定如何操作。
1）若ignore=y,Oracle不執行CREATE TABLE語句,直接將資料插入到表中,如果插入的記錄違背了約束條件,比如主鍵約束,則出錯的記錄不會插入,但合法的記錄會添加到表中。
2）若ignore=n,Oracle不執行CREATE TABLE語句,同時也不會將資料插入到表中,而是忽略該表的錯誤,繼續恢複下一個表。

4、indexes選項
在恢複資料的過程中,若indexes=n,則表上的索引不會被恢複,但是主鍵對應的唯一索引將無條件恢複,這是為了保證資料的完整性。

5、匯出/匯入與字元集
進行資料的匯入匯出時，要注意關於字元集的問題。對於單一位元組字元集(例如US7ASCII),恢複時,資料庫自動轉換為該會話的字元集(NLS_LANG參數); 對於多位元組字元集(例如ZHS16CGB231280),恢複時,應盡量使字元集相同(避免轉換),如果要轉換,目標資料庫的字元集應是輸出資料庫字元集的超集。
在EXP/IMP過程中需要注意四個字元集的參數:匯出端的用戶端字元集、匯出端資料庫字元集、匯入端的用戶端字元集、匯入端資料庫字元集。通常在匯出時最好把用戶端字元集設定得和資料庫端相同。當進行資料匯入時,主要有以下兩種情況:
1）來源資料庫和目標資料庫具有相同的字元集設定
這時,只需設定匯出和匯入端的用戶端NLS_LANG等於資料庫字元集即可。
2）來源資料庫和目標資料庫字元集不同
先將匯出端用戶端的NLS_LANG設定成和匯出端的資料庫字元集一致,匯出資料,然後將匯入端用戶端的NLS_LANG設定成和匯出端一致,匯入資料,這樣轉換隻發生在資料庫端,而且只發生一次。
如果imp和exp字元集不同, 匯入會失敗, 可以改變unix環境變數或者NT註冊表裡NLS_LANG相關資訊，匯入完成後再改回來.

6、資料庫物件有主外鍵約束
不符合主外鍵約束時, 資料會匯入失敗, 解決辦法:
1）先匯入主表(父表), 再匯入從表(子表)
2）disable目標匯入對象的主外鍵約束, 匯入資料後, 再enable它們

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

8、匯出和匯入許可權
需要有EXP_FULL_DATABASE 和 IMP_FULL_DATABASE角色許可權。

9、匯入大表( 大於80M ) 時, 儲存分配失敗
預設的EXP時, compress = Y, 也就是把所有的資料壓縮在一個資料區塊上.
匯入時, 如果不存在連續一個大資料區塊, 則會匯入失敗. 匯出80M以上的大表時, 記得compress= N, 則不會引起這種錯誤。

Oracle exp/imp資料匯入匯出工具基本用法