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

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

檢驗透過檢查共享池的方法能否得到問題SQL。

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

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

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

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

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

雖然在第三篇文章中，已經實現了目標資料的匯入，但是並未採用當時計劃使用的訪問共享池獲取錯誤SQL的方式。

現在打算驗證一下，透過訪問V$SQL的方式是否能夠達到同樣的獲取失敗SQL的目的。

下面刪除SHGOV_ORDER和SHGOV_ORDER_BAK表，重新建立測試環境：

SQL> DROP TABLE SHGOV_ORDER;

表已丟棄。

SQL> DROP TABLE SHGOV_ORDER_BAK;

表已丟棄。

利用imp工具構建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月 20 01:26:18 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也會被共享池所記錄下來，下面就可以用原表的NUMBRE型別來構建SHGOV_ORDER_BAK表，從而導致錯誤發生在觸發器中：

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

表已建立。

根據第二篇文章的測試已經可以確定，當匯入時資料的長度超過表欄位的限制時，即使是BEFORE觸發器也不會觸發。因此需要修改SHGOV_ORDER表的所有NUMBER型別欄位，確保觸發器觸發之前的資料型別檢測可以透過，使得BEFORE觸發器可以觸發。

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 );

表已更改。

下面建立一個BEFORE INSERT觸發器，採用動態的方式來插入資料，否則V$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 /

觸發器已建立

為了避免共享池中已經存在的SQL的影響，匯入前先清空共享池：

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系統已更改。

下面可以嘗試匯入：

[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月 20 17:56:25 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才能放到共享池中。

看來還需要透過修改觸發器的工作方式，不過如果改成字串方式，那麼獲得的記錄就太多了，為了得到需要的記錄，修改一下觸發器的實現：

SQL> CREATE SEQUENCE S_SEQ;

序列已建立。

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL
2 BEFORE INSERT ON SHGOV_ORDER
3 FOR EACH ROW
4 DECLARE
5     V_NUM NUMBER;
6 BEGIN
7     SELECT S_SEQ.NEXTVAL INTO V_NUM FROM DUAL;
8     IF V_NUM = 31323 THEN
9             EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
10             (
11                     TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
12                     UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
13             )
14             VALUES
15             (
16                     ''' || :NEW.TRADE_RATE || ''', ''' || :NEW.ORDER_AMOUONT || ''', '''
17                     || :NEW.SEND_AMOUNT || ''', ''' || :NEW.RECEIVE_AMOUNT || ''', '''
18                     || :NEW.UNIT_PRICE || ''', ''' || :NEW.MAX_PRICE || ''', '''
19                     || :NEW.MIN_PRICE || ''', ''' || :NEW.PRICE_RATE || ''')';
20     END IF;
21 END;
22 /

觸發器已建立

清除環境：

SQL> TRUNCATE TABLE SHGOV_ORDER_BAK;

表已截掉。

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系統已更改。

下面再次進行匯入操作：

[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月 20 18:32:36 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 6
ORA-04088: 觸發器 'TEST.FIND_ERR_SQL' 執行過程中出錯
IMP-00028: 上一個表的部分匯入已回退: 回退 31322 行
IMP-00009: 匯出檔案異常結束
成功終止匯入，但出現警告。

檢查V$SQL，看看是否捕獲了問題SQL語句：

SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'INSERT INTO SHGOV_ORDER_BAK%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------
INSERT INTO SHGOV_ORDER_BAK ( TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT, UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE ) VALUES ( '-53525351485153525352.535299ED98~m~m77311951', '-311951531753535353535349535353535353535345000000000000000000000000000000000000', '-505353535353535252524948536449.535152534552', '-000000000000000000000000000000000000', '-.00000000000000195153175353535353535253535353535352505049', '.00000000000000000000000000000033293311.,', '-~', '-515550504648000000000000')

顯然，透過共享池捕獲錯誤SQL也是一個可行的方法。

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

相關文章