前不久從一個資料庫執行匯出操作時報錯，透過直接路徑方式跳過後，匯入時候再次報錯。推測是由於源資料庫出現的異常導致表中資料超過表定義的精度。

由於源資料庫中錯誤記錄已經被刪除，因此只能想辦法從匯出的dmp檔案中獲取錯誤的記錄。

匯出、匯入過程的描述可以參考：

EXP在9R2上匯出時報錯ORA-3113和ORA-24324：http://yangtingkun.itpub.net/post/468/460647

EXP在9R2上匯出時報錯ORA-3113和ORA-24324（二）：http://yangtingkun.itpub.net/post/468/460831

匯入時報錯如下：

$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on 星期三 5月 7 23:12:20 2008

連線到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

經由常規路徑匯出由EXPORT:V09.02.00建立的檔案

警告: 此物件由 SHGOV 匯出, 而不是當前使用者

已經完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的匯入
. 正在將SHGOV的物件匯入到 TEST
. . 正在匯入表 "SHGOV_ORDER"
IMP-00058: 遇到 ORACLE 錯誤 1438
ORA-01438: 值大於此列指定的允許精確度
IMP-00028: 上一個表的部分匯入已回退: 回退 31322 行
IMP-00009: 匯出檔案異常結束
成功終止匯入，但出現警告。

根據這個錯誤，是無法判斷問題到底出現在哪一列上，也看不到導致錯誤資料。不過由於匯出過程中出現了1438錯誤，因此首先相當的方法是利用系統錯誤觸發器獲取插入失敗記錄的SQL語句。

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期三 5月 7 22:54:26 2008

連線到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE TABLE T_LOG (EXECUTE_DATE DATE, SQL_STATMENT CLOB);

表已建立。

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL AFTER SERVERERROR ON DATABASE
2 DECLARE
3     V_SQL_OUT ORA_NAME_LIST_T;
4     V_NUM NUMBER;
5     V_SQL_STATMENT VARCHAR2(32767);
6 BEGIN
7     IF IS_SERVERERROR(1438) THEN
8             V_NUM := ORA_SQL_TXT(V_SQL_OUT);
9             FOR I IN 1 .. V_NUM LOOP
10                     V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);
11             END LOOP;
12             INSERT INTO T_LOG (EXECUTE_DATE, SQL_STATMENT)
13             VALUES (SYSDATE, V_SQL_STATMENT);
14     END IF;
15 END;
16 /

觸發器已建立

再次執行匯入命令後，查詢T_LOG表中的記錄：

SQL> SET LONG 10000
SQL> SELECT SQL_STATMENT FROM T_LOG;

SQL_STATMENT
------------------------------------------------------------------------------
INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "SHGOV_ORDER" ("ORDER_ITEM_ID", "PLAT_ID
", "PRODUCT_ID", "CODE", "C_NAME_CHN", "MEDICAL_ID", "MEDICAL_CODE", "TRADE_NAME
", "USE_UNIT", "USED_NAME", "MANUFACTURE_ID", "MANUFACTURE_NAME", "MANUFACTURE_A
BBR", "M_SPELL_ABBR", "STAND_RATE", "SPEC", "E_NAME_CHN", "JX_NAME_CHN", "WRAP_N
AME", "TAX_PRICE", "NATIONAL_RETAIL_PRICE", "TRADE_RATE", "MED_INSURE", "ORDER_I
D", "ORDER_AMOUONT", "SEND_AMOUNT", "RECEIVE_AMOUNT", "UNIT_PRICE", "SOURCE_TYPE
", "MAX_PRICE", "HIS_NAME", "HIS_ABBR", "HIS_ID", "DEALER_NAME", "DEALER_ID", "D
EALER_ABBR", "MIN_PRICE", "SENDER_NAME", "SENDER_ID", "SENDER_ABBR", "CREATE_DAT
E", "SENDE_DATE", "PRICE_RATE") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,
:11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,
:27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42,
:43)

Oracle匯入過程使用了繫結變數，顯然這種方式是得不到錯誤的資料的。要想獲得繫結變數的內容，可以設定10046 EVENTS LEVEL 12，不過這種方式的代價太大，而且獲得的最終trace檔案也會十分巨大，想要從中尋找錯誤的資料，肯定也是十分的困難。

那麼就只好採用一個笨辦法，將資料在插入到目標表之前插入到一張記錄表中。根據錯誤資訊可以確定，導致錯誤產生的資料型別應該是NUMBER型別，因此記錄表可以根據源表來生成，並將源表上NUMBER型別的精度都去掉，確保錯誤的資料可以正常的插入到記錄表中：

SQL> create table shgov_order_bak as select * from shgov_order;

表已建立。

SQL> select column_name, data_type, data_precision, data_scale
2 from user_tab_columns
3 where table_name = 'SHGOV_ORDER_BAK'
4 and data_type = 'NUMBER';

COLUMN_NAME                    DATA_TYPE       DATA_PRECISION DATA_SCALE
------------------------------ --------------- -------------- ----------
TRADE_RATE                     NUMBER                      10          2
ORDER_AMOUONT                  NUMBER
SEND_AMOUNT                    NUMBER
RECEIVE_AMOUNT                 NUMBER
UNIT_PRICE                     NUMBER                      13          3
MAX_PRICE                      NUMBER                      11          3
MIN_PRICE                      NUMBER                      11          3
PRICE_RATE                     NUMBER                      12          3

已選擇8行。

SQL> alter table shgov_order modify
2 (trade_rate number,
3 unit_price number,
4 max_price number,
5 min_price number,
6 price_rate number);

表已更改。

為了方便的找到最後一條錯誤記錄，在記錄表中新增一個記錄ID：

SQL> alter table shgov_order_bak add id number;

表已更改。

SQL> create sequence s_id;

序列已建立。

下面建立一個自治事務的觸發器，確保源表插入失敗回滾後，記錄表中的資訊可以保留下來：

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL
2 BEFORE INSERT ON SHGOV_ORDER
3 FOR EACH ROW
4 DECLARE
5     PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7     INSERT INTO SHGOV_ORDER_BAK VALUES
8     (
9             :NEW.ORDER_ITEM_ID, :NEW.PLAT_ID, :NEW.PRODUCT_ID, :NEW.CODE,
10             :NEW.C_NAME_CHN, :NEW.MEDICAL_ID, :NEW.MEDICAL_CODE, :NEW.TRADE_NAME,
11             :NEW.USE_UNIT, :NEW.USED_NAME, :NEW.MANUFACTURE_ID,
12             :NEW.MANUFACTURE_NAME, :NEW.MANUFACTURE_ABBR, :NEW.M_SPELL_ABBR,
13             :NEW.STAND_RATE, :NEW.SPEC, :NEW.E_NAME_CHN, :NEW.JX_NAME_CHN,
14             :NEW.WRAP_NAME, :NEW.TAX_PRICE, :NEW.NATIONAL_RETAIL_PRICE,
15             :NEW.TRADE_RATE, :NEW.MED_INSURE, :NEW.ORDER_ID, :NEW.ORDER_AMOUONT,
16             :NEW.SEND_AMOUNT, :NEW.RECEIVE_AMOUNT, :NEW.UNIT_PRICE,
17             :NEW.SOURCE_TYPE, :NEW.MAX_PRICE, :NEW.HIS_NAME, :NEW.HIS_ABBR,
18             :NEW.HIS_ID, :NEW.DEALER_NAME, :NEW.DEALER_ID, :NEW.DEALER_ABBR,
19             :NEW.MIN_PRICE, :NEW.SENDER_NAME, :NEW.SENDER_ID, :NEW.SENDER_ABBR,
20             :NEW.CREATE_DATE, :NEW.SENDE_DATE, :NEW.PRICE_RATE, S_ID.NEXTVAL
21     );
22     COMMIT;
23 END;
24 /

觸發器已建立

下面再次執行匯入操作：

$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on 星期四 5月 8 00:19:39 2008

連線到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

經由常規路徑匯出由EXPORT:V09.02.00建立的檔案

警告: 此物件由 SHGOV 匯出, 而不是當前使用者

已經完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的匯入
. 正在將SHGOV的物件匯入到 TEST
. . 正在匯入表 "SHGOV_ORDER"
IMP-00058: 遇到 ORACLE 錯誤 1438
ORA-01438: 值大於此列指定的允許精確度
ORA-06512: 在"TEST.FIND_ERR_SQL", line 4
ORA-04088: 觸發器 'TEST.FIND_ERR_SQL' 執行過程中出錯
IMP-00028: 上一個表的部分匯入已回退: 回退 31322 行
IMP-00009: 匯出檔案異常結束
成功終止匯入，但出現警告。

發現錯誤資訊發生了變化，居然插入記錄表也報錯。而記錄表中所有的數值型別都已經是NUMBER型別，即使這樣仍然會報錯，看來資料確實比較奇怪。

看來只能將表的所有數值型別的欄位改為VARCHAR2(4000)：

SQL> ALTER TABLE SHGOV_ORDER MODIFY
2 (
3     TRADE_RATE VARCHAR2(4000),
4     ORDER_AMOUONT VARCHAR2(4000),
5     SEND_AMOUNT VARCHAR2(4000),
6     RECEIVE_AMOUNT VARCHAR2(4000),
7     UNIT_PRICE VARCHAR2(4000),
8     MAX_PRICE VARCHAR2(4000),
9     MIN_PRICE VARCHAR2(4000),
10     PRICE_RATE VARCHAR2(4000)
11 );

表已更改。

SQL> TRUNCATE TABLE SHGOV_ORDER_BAK;

表已截掉。

SQL> DROP SEQUENCE S_ID;

序列已丟棄。

SQL> CREATE SEQUENCE S_ID;

序列已建立。

再次執行匯入：

$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on 星期四 5月 8 00:41:19 2008

連線到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

經由常規路徑匯出由EXPORT:V09.02.00建立的檔案

警告: 此物件由 SHGOV 匯出, 而不是當前使用者

已經完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的匯入
. 正在將SHGOV的物件匯入到 TEST
. . 正在匯入表 "SHGOV_ORDER"
IMP-00058: 遇到 ORACLE 錯誤 1722
ORA-01722: 無效數字
ORA-06512: 在"TEST.FIND_ERR_SQL", line 4
ORA-04088: 觸發器 'TEST.FIND_ERR_SQL' 執行過程中出錯
IMP-00028: 上一個表的部分匯入已回退: 回退 31322 行
IMP-00009: 匯出檔案異常結束
成功終止匯入，但出現警告。

錯誤資訊再次改變，看來這次是在將數值型別轉化為NUMBER型別的時候出現了錯誤。基本上可以確定，問題多半是由於邏輯損壞造成儲存的資料異常，目前這個值已經無法轉換為NUMBER型別了。

獲取導致匯入失敗的資料

相關文章