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

由於源資料庫中錯誤記錄已經被刪除，因此只能想辦法從匯出的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

上一篇透過觸發器的方式沒有獲取到具體的錯誤資料，這篇繼續修正方法繼續找出異常的資料：

獲取導致匯入失敗的資料：http://yangtingkun.itpub.net/post/468/461401

BEFORE觸發器修正資料錯誤：http://yangtingkun.itpub.net/post/468/461506

由於上一篇的諸多測試已經將各個表的結構修改的比較混亂，下面重建所有的測試結構：

SQL> DROP TABLE SHGOV_ORDER_BAK;

表已丟棄。

SQL> DROP TABLE SHGOV_ORDER;

表已丟棄。

SQL> CONN / AS SYSDBA
已連線。
SQL> DROP TRIGGER FIND_ERR_SQL;

觸發器已丟棄

SQL> DROP TABLE T_LOG;

表已丟棄。

利用匯入工具重建SHGOV_ORDER表：

[oracle@bjtest data]$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y rows=n

Import: Release 9.2.0.4.0 - Production on 星期五 5月 9 17:48:37 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
IMP-00009: 匯出檔案異常結束
成功終止匯入，但出現警告。

下面建立用來儲存觸發器插入記錄的表：

SQL> CREATE TABLE SHGOV_ORDER_BAK AS
2 SELECT TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
3 UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
4 FROM SHGOV_ORDER;

表已建立。

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

表已建立。

懷疑問題出在NUMBER型別的欄位上，因此目標表只包含了NUMBER型別的欄位，而且為了方便查詢，將系統觸發器的日誌也放到當前使用者下。

由於採用靜態SQL的方式，從系統觸發器獲取的SQL語句都是繫結變數的形式，沒有辦法看到引發錯誤的資料，因此觸發器修改一下，改為動態SQL的方式，希望採用這種方式可以獲取到錯誤的資料。

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   EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
8   (
9    TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
10    UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
11   )
12   VALUES
13   (
14    ' || :NEW.TRADE_RATE || ', ' || :NEW.ORDER_AMOUONT || ', '
15    || :NEW.SEND_AMOUNT || ', ' || :NEW.RECEIVE_AMOUNT || ', '
16    || :NEW.UNIT_PRICE || ', ' || :NEW.MAX_PRICE || ', '
17    || :NEW.MIN_PRICE || ', ' || :NEW.PRICE_RATE || ')';
18   COMMIT;
19 END;
20 /

觸發器已建立

最後建立系統觸發器，並對原有程式碼進行簡單的調整，去掉了對1438錯誤的判斷，這樣發生任何的錯誤都可以捕獲。

SQL> CONN / AS SYSDBA
已連線。
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             V_NUM := ORA_SQL_TXT(V_SQL_OUT);
8             FOR I IN 1 .. V_NUM LOOP
9                     V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);
10             END LOOP;
11             INSERT INTO TEST.T_LOG (EXECUTE_DATE, SQL_STATMENT)
12             VALUES (SYSDATE, V_SQL_STATMENT);
13 END;
14 /

觸發器已建立

由於IMP採用繫結變數的方式，捕獲IMP的錯誤沒有意義，而且由於Oracle會先驗證插入資料的合法性，然後呼叫BEFORE觸發器，因此匯入錯誤記錄時，BEFORE觸發器是不會被觸發的。所以，這裡需要修改一下SHGOV_ORDER表的數值型別，將其轉化為NUMBER型別，這樣讓精度問題出現在觸發器中，使得系統觸發器可以捕獲這個錯誤。

SQL> ALTER TABLE SHGOV_ORDER MODIFY
2 (
3     TRADE_RATE NUMBER,
4     UNIT_PRICE NUMBER,
5     MAX_PRICE NUMBER,
6     MIN_PRICE NUMBER,
7     PRICE_RATE NUMBER
8 );

表已更改。

下面執行匯入：

[oracle@bjtest data]$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on 星期五 5月 9 18:15:08 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 錯誤 917
ORA-00917: 缺少逗號
ORA-06512: 在"TEST.FIND_ERR_SQL", line 4
ORA-04088: 觸發器 'TEST.FIND_ERR_SQL' 執行過程中出錯
IMP-00028: 上一個表的部分匯入已回退: 回退 31322 行
IMP-00009: 匯出檔案異常結束
成功終止匯入，但出現警告。

檢視記錄SQL的結果：

SQL> CONN TEST/TEST
已連線。
SQL> SET LONG 100000
SQL> SELECT SQL_STATMENT FROM T_LOG;

SQL_STATMENT
--------------------------------------------------------------------------------
CREATE TABLE "SHGOV_ORDER" ("ORDER_ITEM_ID" CHAR(24) NOT NULL ENABLE, "PLAT_ID"
CHAR(24), "PRODUCT_ID" CHAR(24), "CODE" VARCHAR2(50), "C_NAME_CHN" VARCHAR2(300)
.
.
.
ANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPA
CE "SH_GOV" LOGGING NOCOMPRESS

CREATE TABLE "SHGOV_ORDER" ("ORDER_ITEM_ID" CHAR(24) NOT NULL ENABLE, "PLAT_ID"
CHAR(24), "PRODUCT_ID" CHAR(24), "CODE" VARCHAR2(50), "C_NAME_CHN" VARCHAR2(300)
.
.
.
ATE, "SENDE_DATE" DATE, "PRICE_RATE" NUMBER(12, 3)) PCTFREE 10 PCTUSED 40 INITR
ANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
LOGGING NOCOMPRESS

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)

看來即使是讓觸發器報錯，捕獲到的最終出錯SQL也是匯入的SQL語句，看來試圖利用觸發器捕獲異常資料的方法，基本上是行不通的。

獲取導致匯入失敗的資料（二）

相關文章