IMP-00009: abnormal end of export file

IMP-00009: abnormal end of export file

一、概述

最近在測試環境的一個oracle資料庫上面，使用exp將表匯出沒有問題，而將匯出的檔案使用imp匯入時卻出現瞭如下錯誤。
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.

經過反覆實驗，終於找出問題出現的原因，是由以下幾點共同造成的：
a. 資料庫中引數deferred_segment_creation設定的是預設值true，即建立表的時候不立即分配段，等有行的時候才會分配段。
b. 匯出的表中有分割槽表，而恰好該分割槽表存在分割槽沒有行的情況，即有的分割槽沒有分配段。
c. 匯出時使用了direct=true。

解決辦法直接看(三、解決辦法)

二、問題復現

1. 準備工作

在測試庫中準備兩個使用者，tom(匯出的使用者)，jerry(匯入的使用者)，分別給予其最大的許可權。
SQL> create user tom identified by tom;
SQL> grant dba to tom;
SQL> create user jerry identified by jerry;
SQL> grant dba to jerry;

2. 檢查資料庫中引數deferred_segment_creation

SQL> show parameter deferred
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

可以看到該引數是true，預設值。

3. 建立測試表

SQL> create table tom.t_normal as select * from scott.emp;  // 建立一張普通表，並且有行

SQL> create table tom.t_norows as select * from scott.emp where 1=0;  // 建立一張空表

SQL> create table tom.t_par(id number, name varchar2(10))
partition by list(id)
(partition p01 values(1),
partition p02 values(default));  // 建立一張分割槽表，兩個分割槽

SQL> insert into tom.t_par values(1, 'aa');  // 往分割槽p01插入一條資料
SQL> commit;

至此，tom使用者下有三張表，t_normal是普通表，t_norows是一張普通的空表，t_par是分割槽表。

透過下面的sql查到tom使用者下只有兩個segment，空表和分割槽表中無資料的分割槽都沒有建立段。

SQL> select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM';
OWNER      SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE
---------- -------------------- ------------------------------ ------------------
TOM        T_NORMAL                                            TABLE
TOM        T_PAR                P01                            TABLE PARTITION

4. 使用tom對錶進行匯出

exp tom/tom file=tom.dmp log=tom_exp.log direct=true

匯出日誌如下(省略部分無關內容)：
. . exporting table                       T_NORMAL         14 rows exported
. . exporting table                       T_NOROWS          0 rows exported
. . exporting table                          T_PAR
. . exporting partition                            P01          1 rows exported
. . exporting partition                            P02          0 rows exported
Export terminated successfully without warnings.

5. 使用jerry對檔案進行匯入

imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

匯入日誌如下(省略部分無關內容)：
. . importing table                     "T_NORMAL"         14 rows imported
. . importing table                     "T_NOROWS"          0 rows imported
. . importing partition                  "T_PAR":"P01"          1 rows imported
. . importing partition                  "T_PAR":"P02"
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.
生產上面出現的錯誤在這裡就得到復現了。而且是在匯入"T_PAR":"P02"出現的錯誤，這個正好印證了前面的觀點。

三、解決辦法

解決辦法有以下兩種(任一即可)：
a. 使用exp匯出的時候不要加direct=true
b. 設定資料庫的引數deferred_segment_creation為false(注意：這個引數隻影響新建的分割槽表，老的分割槽表匯出再匯入仍然會報錯！)

四、有時間、有興趣的讀者可以接著做實驗

可能大家會問，你怎麼知道是分割槽表的問題，又怎麼知道是direct=true的問題，又怎麼知道是引數deferred_segment_creation的問題？接下來我一一驗證。

1. 清空jerry的表，匯出tom使用者下表t_normal,t_norows，再匯入到jerry使用者中

SQL> drop user jerry cascade;  // 透過重建jerry使用者來清空jerry的表
SQL> create user jerry identified by jerry;
SQL> grant dba to jerry;

exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_normal,t_norows
匯出日誌：
. . exporting table                       T_NORMAL         14 rows exported
. . exporting table                       T_NOROWS          0 rows exported
Export terminated successfully without warnings.

imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry
匯入日誌：
. . importing table                     "T_NORMAL"         14 rows imported
. . importing table                     "T_NOROWS"          0 rows imported
Import terminated successfully without warnings.

可以看到對這兩張表匯入是沒有問題的

2. 清空jerry的表，匯出tom使用者下表t_par，再匯入到jerry使用者中

清空jerry的表的操作請看上面的步驟

exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_par
匯出日誌：
. . exporting table                          T_PAR
. . exporting partition                            P01          1 rows exported
. . exporting partition                            P02          0 rows exported
Export terminated successfully without warnings.

imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry
匯入日誌：
. importing TOM's objects into JERRY
. . importing partition                  "T_PAR":"P01"          1 rows imported
. . importing partition                  "T_PAR":"P02"
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.

可以看到問題就出在對這張分割槽表的匯入上面了

3. 清空jerry的表，重新匯出tom使用者下表t_par，再匯入到jerry使用者中(這次匯出不加引數direct=true)

清空jerry的表的操作請看上面的步驟

exp tom/tom file=tom.dmp log=tom_exp.log tables=t_par
匯出日誌：
. . exporting table                          T_PAR
. . exporting partition                            P01          1 rows exported
. . exporting partition                            P02          0 rows exported
Export terminated successfully without warnings.

imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry
匯入日誌：
. importing TOM's objects into JERRY
. . importing partition                  "T_PAR":"P01"          1 rows imported
. . importing partition                  "T_PAR":"P02"          0 rows imported
Import terminated successfully without warnings.

可以看到這次匯入沒有任何問題，也就是說不加direct=true直接可以解決問題，但是如果我非要加這個引數呢，或者說這個命令寫死到程式中了，沒辦法改怎麼辦？處理辦法看下面第6條。

4. 清空jerry的表，在tom.t_par的p02分割槽中插入一條資料，重新匯出tom使用者下表t_par，再匯入到jerry使用者中(這次匯出依然加引數direct=true)

清空jerry的表的操作請看上面的步驟

SQL> insert into tom.t_par values(2, 'bb');  // 往分割槽p02插入一條資料
SQL> commit;

透過下面的sql查到t_par兩個分割槽都有段了。

SQL> select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM';
OWNER      SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE
---------- -------------------- ------------------------------ ------------------
TOM        T_PAR                P01                            TABLE PARTITION
TOM        T_PAR                P02                            TABLE PARTITION
TOM        T_NORMAL                                            TABLE

 

exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_par
匯出日誌：
. . exporting table                          T_PAR
. . exporting partition                            P01          1 rows exported
. . exporting partition                            P02          1 rows exported
Export terminated successfully without warnings.

imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry
匯入日誌：
. importing TOM's objects into JERRY
. . importing partition                  "T_PAR":"P01"          1 rows imported
. . importing partition                  "T_PAR":"P02"          1 rows imported
Import terminated successfully without warnings.

可以看到分割槽表中所有分割槽都有資料的話，匯入就沒有任何問題，

5. 驗證deferred_segment_creation引數對其的影響

清空jerry的表的操作請看上面的步驟

修改資料庫中引數deferred_segment_creation為false
SQL> alter system set deferred_segment_creation=false;

重建tom使用者的t_par表，讓其一個分割槽有資料，另外一個分割槽無資料
SQL> drop table tom.t_par;

SQL> create table tom.t_par(id number, name varchar2(10))
partition by list(id)
(partition p01 values(1),
partition p02 values(default));  // 建立一張分割槽表，兩個分割槽

SQL> insert into tom.t_par values(1, 'aa');  // 往分割槽p01插入一條資料
SQL> commit;

透過下面的sql查到t_par兩個分割槽都有段了，即使p02分割槽裡面沒有資料

SQL> select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM';
OWNER      SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE
---------- -------------------- ------------------------------ ------------------
TOM        T_PAR                P01                            TABLE PARTITION
TOM        T_PAR                P02                            TABLE PARTITION
TOM        T_NORMAL                                            TABLE

exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_par
匯出日誌：
. . exporting table                          T_PAR
. . exporting partition                            P01          1 rows exported
. . exporting partition                            P02          0 rows exported
Export terminated successfully without warnings.

imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry
匯入日誌：
. importing TOM's objects into JERRY
. . importing partition                  "T_PAR":"P01"          1 rows imported
. . importing partition                  "T_PAR":"P02"          0 rows imported
Import terminated successfully without warnings.

可以看到將引數deferred_segment_creation修改為false匯入也正常，但是這隻適用於新建的分割槽表，對於已經存在的分割槽表，依然會匯入失敗。處理辦法看下面第6條。

6. 接下來回答上面第3和5步中的問題

如果想用exp，imp進行匯出匯入，匯出的時候又必須加direct=true，而且匯出的表中包含分割槽表，並且該分割槽表中存在分割槽沒有段的情況。那怎麼辦？
光是將引數deferred_segment_creation修改為false不夠，因為這隻影響新建的表，要想對老的表也生效，可以採取下面的辦法。

6.1. 將引數deferred_segment_creation修改為false
SQL> alter system set deferred_segment_creation=false;

6.2. 使用exp對分割槽表進行匯出(只有那些分割槽表中存在分割槽沒有分配段的才需要匯出)，注意不要加direct=true
exp tom/tom file=tom.dmp log=tom_exp.log tables=t_par

6.3. 刪除該分割槽表
SQL> drop table tom.t_par;

6.4. 使用imp對其進行匯入
imp tom/tom file=tom.dmp log=tom_imp.log full=y

匯入日誌
. importing TOM's objects into TOM
. . importing partition                  "T_PAR":"P01"          1 rows imported
. . importing partition                  "T_PAR":"P02"          0 rows imported
Import terminated successfully without warnings.

匯入後檢視段的情況

SQL> select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM';
OWNER      SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE
---------- -------------------- ------------------------------ ------------------
TOM        T_PAR                P02                            TABLE PARTITION
TOM        T_PAR                P01                            TABLE PARTITION

可以看到現在即使P02分割槽中沒有行，也分配了段。這是由於我先前已經將引數deferred_segment_creation設定成了false，並且刪除了表，imp在執行過程中，會先建立表然後插入資料，在建立表時，每個分割槽都會分配段。也就是說只需要解決老的分割槽表中段沒有分配的情況，後面就不會碰到IMP-00009。

五、總結

只有在分割槽表中存在分割槽沒有分配段，而且在匯出時使用了direct=true引數，這兩種情況在一起才會造成我這個IMP-00009這個錯誤。對於其它的普通表，不管有沒有分配段，是否使用direct=true都不會造成這個錯誤。

我在分析IMP-00009這個問題的時候，首先日誌紀錄只有一行，就寫"IMP-00009: abnormal end of export file"，第一時間去查匯出的日誌，"Export terminated successfully without warnings."，匯出的日誌沒有顯示任何異常。這就把我整懵逼了。然後我開始求助於萬能的網際網路，查了一圈下來，沒有找到任何解決方案，其實不是大牛不解答，而是問問題的人提供的資訊太少了，你就提供個錯誤日誌，比方說我這次碰到的問題，假設你只給個錯誤日誌，大牛打死也復現不出來問題，那就談不上去解決問題了。而往往當我們把整個問題都描述清楚了，問題大機率就迎刃而解了。

文章中涉及到的相關資訊備註：
deferred_segment_creation：延遲段建立，上面已經透過實驗介紹的很清楚了。
direct=true：匯出資料時不經過buffer cache，這個引數是個天坑，bug極多，匯出時儘量不要用。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2019-08-01 06:00 ~ 2019-08-31 24:00 在西安完成 ● 最新修改時間：2019-08-01 06:00 ~ 2019-08-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端 掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................