oracle Imp和exp以及匯入常見的錯誤____oracle

 

一 1) 資料庫物件已經存在

一般情況, 匯入資料前應該徹底刪除目標資料下的表, 序列, 函數/過程,觸發器等;   

資料庫物件已經存在, 按預設的imp參數, 則會匯入失敗 

如果用了參數ignore=y, 會把exp檔案內的資料內容匯入

如果表有唯一關鍵字的約束條件, 不合條件將不被匯入

如果表沒有唯一關鍵字的約束條件, 將引起記錄重複

 

(2) 資料庫物件有主外鍵約束

      不符合主外鍵約束時, 資料會匯入失敗 

      解決辦法: 先匯入主表, 再匯入依存表

disable目標匯入對象的主外鍵約束, 匯入資料後, 再enable它們

 

 

 

(3)  許可權不夠

 

如果要把A使用者的資料匯入B使用者下, A使用者需要有imp_full_database許可權

 

(4)  匯入大表( 大於80M ) 時, 儲存分配失敗

      預設的EXP時, compress = Y, 也就是把所有的資料壓縮在一個資料區塊上.

      匯入時, 如果不存在連續一個大資料區塊, 則會匯入失敗.

      匯出80M以上的大表時, 記得compress= N, 則不會引起這種錯誤.

 

 

(5) imp和exp使用的字元集不同

      如果字元集不同, 匯入會失敗, 可以改變unix環境變數或者NT註冊表裡NLS_LANG相關資訊.

 

      匯入完成後再改回來.

 

(6) imp和exp版本不能往上相容

imp可以成功匯入低版本exp產生的檔案, 不能匯入高版本exp產生的檔案

 

 

 

表模式備份： 

 

[oracle@roy orcl]$ exp david/david rows=y indexes=n compress=n buffer=65536 file=exp_tianle_090101.dmp log=exp_tianle_090101.log tables=(tianle); 

 

使用者模式備份： 

 

[oracle@roy orcl]$ exp david/david owner=david rows=y indexes=n compress=n buffer=65536 file=exp_david__090101.dmp log=exp_david_090101.log;

 

完全模式備份： 

 

[oracle@roy orcl]$ exp david/david rows=y indexes=n compress=n buffer=65536 full=y file=exp_fulldatabase_090101.dmp log=exp_fulldatabase_090101.log; 

 

表模式恢複： 

 

[oracle@roy orcl]$ imp david/david fromuser=david touser=david rows=y indexes=n commit=y   buffer=65536 file=exp_tianle_090101.dmp log=imp_tianle_090101.log tables=(tianle); 

 

使用者模式恢複： 

 

[oracle@roy orcl]$ imp david/david fromuser=david touser=david rows=y indexes=n commit=y buffer=65536 file=exp_tianle_090101.dmp log=exp_tianle_090101.log; 

 

全庫模式恢複： 

 

[oracle@roy orcl]$ imp david/david rows=y indexes=n commit=y  full=y ignore=y buffer=65536 file=/tmp/exp_fulldatabase_090101.dmp log=/tmp/imp.log;

 

1.4 exp/imp 與 expdp/impdp 功能上的區別

（1）把使用者usera的對象導到使用者userb,用法區別在於fromuser=usera touser=userb ,remap_schema='usera':'userb' 。例如

            imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log;

            impdp system/passwd directory=expdp dumpfile=expdp.dmp remap_schema='usera':'userb' logfile=/oracle/exp.log;

 

（2）更換資料表空間，用exp/imp的時候，要想更改表所在的資料表空間，需要手工去處理一下，如alter table xxx move tablespace_new之類的操作。用impdp只要用remap_tablespace='tabspace_old':'tablespace_new'

 

（3）當指定一些表的時候，使用exp/imp 時，tables的用法是 tables=('table1','table2','table3')。expdp/impdp用法是tables='table1','table2','table3'。

 

（4）是否要匯出資料行

            exp　（ROWS=Y 匯出資料行，ROWS=N 不匯出資料行）

            expdp content（ALL:對象＋匯出資料行，DATA_ONLY：只匯出對象，METADATA_ONLY：只匯出資料的記錄）

二. 使用中的最佳化事項

 

2.1  exp

            通過上面的分析，知道採用direct path可以提高匯出速度。 所以，在使用exp時，就可以採用直接路徑模式。 這種模式有2個相關的參數：DIRECT 和RECORDLENGTH參數。

 

　　DIRECT參數定義了匯出是使用直接路徑方式(DIRECT=Y)，還是常規路徑方式(DIRECT=N)。常規路徑匯出使用SQL SELECT語句從表中抽取資料，直接路徑匯出則是將資料直接從磁碟讀到PGA再原樣寫入匯出檔案，從而避免了SQL命令處理層的資料轉換過程，大大提高了匯出效率。在資料量大的情況下，直接路徑匯出的效率優勢更為明顯，可比常規方法速度提高三倍之多。

 

　　和DIRECT=Y配合使用的是RECORDLENGTH參數，它定義了Export I/O緩衝的大小，作用類似於常規路徑匯出使用的BUFFER參數。建議設定RECORDLENGTH參數為最大I/O緩衝，即65535(64kb)。其用法如下：

            如：exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log

 

 

一些限制如下：

            You cannot use the DIRECT=Y parameter when exporting in tablespace-mode  (i.e. when specifying the parameter TRANSPORT_TABLESPACES=Y).  You can use the DIRECT=Y parameter when exporting in full, user or table mode (i.e.: when specifying FULL=Y or OWNER=scott or TABLES=scott.emp).

--直接路徑不能使用在tablespace-mode

 

The parameter QUERY applies ONLY to conventional path Export. It cannot be specified in a direct path export (DIRECT=Y).

-- 直接路徑不支援query 參數。 query 只能在conventional path模式下使用。

 

In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs.

-如果exp 版本小於8.1.5，不能使用exp 匯入有lob欄位的biao。 不過現在很少有有8版本的資料庫了。 這點可以忽略掉了。

 

The BUFFER parameter applies ONLY to conventional path Export. It has no effect on a direct path Export. This BUFFER parameter specifies the size (in bytes) of the buffer used to fetch rows. It determines the maximum number of rows in an array, fetched by Export.  For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.

-- buffer 選項只對conventional path exp有效。 對於直接路徑沒有影響。 對於直接路徑， 應該設定RECORDLENGTH 參數。

 

The RECORDLENGTH parameter specifies the length (in bytes) of the file record. You can use this parameter to specify the size of the Export I/O buffer (highest value is 64 kb). Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to disk.  It does not affect the operating system file block size.  If you do not define this parameter, it defaults to your platform-dependent value for BUFSIZ (1024 bytes in most cases).

 

invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH

 

-- 對於直接路徑下， RECORDLENGTH參數建議設成64k（65535）。這個值對效能提高比較大。  如：

> exp system/manager FILE=exp_full.dmp LOG=exp_full.log

FULL=y DIRECT=y RECORDLENGTH=65535

> imp system/manager FILE=exp_full.dmp LOG=imp_full.log

FULL=y RECORDLENGTH=65535 

 

 

2.2  IMP

 

　　Oracle Import進程需要花比Export進程數倍的時間將資料匯入資料庫。某些關鍵時刻，匯入是為了應對資料庫的緊急故障恢複。為了減少宕機時間，加快匯入速度顯得至關重要。沒有特效辦法加速一個大資料量的匯入，但我們可以做一些適當的設定以減少整個匯入時間。

 

（1）避免I/O競爭

            Import是一個I/O密集的操作，避免I/O競爭可以加快匯入速度。如果可能，不要在系統高峰的時間匯入資料，不要在匯入資料時運行job等可能競爭系統資源的操作。

（2）增加排序區

　　Oracle Import進程先匯入資料再建立索引，不論INDEXES值設為YES或者NO，主鍵的索引是一定會建立的。建立索引的時候需要用到排序區，在記憶體大小不足的時候，使用暫存資料表空間進行磁碟排序，由於磁碟排序效率和記憶體排序效率相差好幾個數量級。增加排序區可以大大提高建立索引的效率，從而加快匯入速度。

（3）調整BUFFER選項

　　Imp參數BUFFER定義了每一次讀取匯出檔案的資料量，設的越大，就越減少Import進程讀取資料的次數，從而提高匯入效率。BUFFER的大小取決於系統應用、資料庫規模，通常來說，設為百兆就足夠了。其用法如下：

            imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 commit=y feedback=10000 buffer=10240000

（4）使用COMMIT=Y選項

　　COMMIT=Y表示每個資料緩衝滿了之後提交一次，而不是導完一張表提交一次。這樣會大大減少對系統復原段等資源的消耗，對順利完成匯入是有益的。

（5）使用INDEXES=N選項

　　前面談到增加排序區時，說明Imp進程會先匯入資料再建立索引。匯入處理程序中建立使用者定義的索引，特別是表上有多個索引或者資料表特別龐大時，需要耗費大量時間。某些情況下，需要以最快的時間匯入資料，而索引允許後建，我們就可以使用INDEXES=N 只匯入資料不建立索引，從而加快匯入速度。

　　我們可以用INDEXFILE選項產生建立索引的DLL指令碼，再手工建立索引。我們也可以用如下的方法匯入兩次，第一次匯入資料，第二次匯入索引。其用法如下：

imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=y indexes=n

 

imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_index_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=n indexes=y

（6）增加  LARGE_POOL_SIZE

　　如果在init.ora中配置了MTS_SERVICE，MTS_DISPATCHERS等參數，tnsnames.ora中又沒有(SERVER=DEDICATED)的配置，那麼資料庫就使用了共用伺服器模式。在MTS模式下，Exp/Imp操作會用到LARGE_POOL，建議調整LARGE_POOL_SIZE到150M。

 

檢查資料庫是否在MTS模式下：

　　SQL>select distinct server from v$session;

如果傳回值出現none或shared，說明啟用了MTS。