Oracle10g新特性——記錄DML錯誤日誌（二）

當一個DML執行的時候，如果遇到了錯誤，則這條語句會整個回滾，就好像沒有執行過。不過對於一個大的DML而言，如果個別資料錯誤而導致整個語句的回滾，會浪費很多的資源和執行時間，從10g開始Oracle支援記錄DML語句的錯誤，而允許語句自動繼續執行。

這篇介紹DML記錄語句的限制。

Oracle10g新特性——記錄DML錯誤日誌（一）：http://yangtingkun.itpub.net/post/468/479317

 

 

上一篇簡單介紹了DML記錄語句的限制，雖然所有的例子都是利用INSERT語句，但是LOG ERRORS語句並沒有這個限制，UPDATE、DELETE和MERGE都可以使用這個語句。下面要說的才是LOG ERRORS語句的限制。

當發生下面的情況時，錯誤記錄語句無效，Oracle會自動回滾錯誤的語句：

違反延遲約束；

直接路徑的INSERT或MERGE語句違反了唯一約束或唯一索引；

更新操作違反了唯一約束或唯一索引。

下面先看看違反延遲約束的情況：

SQL> DROP TABLE T PURGE;

表已刪除。

SQL> DROP TABLE T_ERROR_LOG PURGE;

表已刪除。

SQL> CREATE TABLE T
  2  (ID NUMBER,
  3  NAME VARCHAR2(10),
  4  AGE NUMBER(3),
  5  CONSTRAINT CK_T_AGE CHECK (AGE < 150)
  6  DEFERRABLE
  7  INITIALLY DEFERRED);

表已建立。

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG')

PL/SQL 過程已成功完成。

SQL> DESC T_ERROR_LOG
 名稱                                                              是否為空? 型別
 ----------------------------------------------------------------- -------- ---------------
 ORA_ERR_NUMBER$                                                            NUMBER
 ORA_ERR_MESG$                                                              VARCHAR2(2000)
 ORA_ERR_ROWID$                                                             ROWID
 ORA_ERR_OPTYP$                                                             VARCHAR2(2)
 ORA_ERR_TAG$                                                               VARCHAR2(2000)
 ID                                                                         VARCHAR2(4000)
 NAME                                                                       VARCHAR2(4000)
 AGE                                                                        VARCHAR2(4000)

下面測試一下LOG ERRORS語句：

SQL> INSERT INTO T VALUES (1, '1234567890A', 5)
  2  LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT 1;

已建立0行。

SQL> SELECT COUNT(*) FROM T_ERROR_LOG;

  COUNT(*)
----------
         1

下面嘗試違反延遲約束：

SQL> INSERT INTO T VALUES (1, 'ABC', 200)
  2  LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT 1;

已建立 1 行。

SQL> COMMIT;
COMMIT
*
第 1 行出現錯誤:
ORA-02091: 事務處理已回退
ORA-02290: 違反檢查約束條件 (YANGTK.CK_T_AGE)

SQL> SELECT COUNT(*) FROM T_ERROR_LOG;

  COUNT(*)
----------
         1

由於延遲約束的檢查在COMMIT時刻進行，而不是在DML發生的時刻，因此不會利用LOG ERRORS語句將違反結果的記錄插入到記錄表中，這也是很容易理解的。

下面看看直接路徑插入違反唯一約束的情況：

SQL> ALTER TABLE T DROP CONSTRAINT CK_T_AGE;

表已更改。

SQL> ALTER TABLE T ADD PRIMARY KEY(ID);

表已更改。

SQL> INSERT /*+ APPEND */ INTO T
  2  SELECT MOD(ROWNUM, 10), SUBSTR(TNAME, 1, 10), 10
  3  FROM TAB
  4  LOG ERRORS INTO T_ERROR_LOG
  5  REJECT LIMIT UNLIMITED;
INSERT /*+ APPEND */ INTO T
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (YANGTK.SYS_C0011606)

SQL> SELECT COUNT(*) FROM T_ERROR_LOG;

  COUNT(*)
----------
         1

直接路徑插入本身就很特殊，在執行過程中會繞過很多常規SQL執行的步驟，因此LOG ERRORS語句對其無效也是可以理解的。

最後來看看更新語句違反唯一約束的情況：

SQL> SELECT * FROM T;

未選定行

SQL> INSERT INTO T
  2  SELECT MOD(ROWNUM, 10), SUBSTR(TNAME, 1, 10), 10
  3  FROM TAB
  4  LOG ERRORS INTO T_ERROR_LOG
  5  REJECT LIMIT UNLIMITED;

已建立10行。

SQL> UPDATE T
  2  SET ID = 1
  3  WHERE ID = 2
  4  LOG ERRORS INTO T_ERROR_LOG
  5  REJECT LIMIT UNLIMITED;
UPDATE T
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (YANGTK.SYS_C0011606)

可以看到，如果更新操作導致了唯一約束或唯一索引衝突，也是不會記錄到錯誤記錄表中的。至於為什麼更新操作會產生這種情況，還沒有想明白，不過主鍵的衝突和其他約束衝突有所區別，Oracle在處理的時候很可能會有所考慮。