10g release 2的新特性--記錄DML錯誤日誌

當一個DML執行的時候，如果遇到了錯誤，不管在這之前成功了多少條記錄，這條語句會整個回滾。以前解決這個問題的辦法只有單獨的執行每條語句，或者用FORALL的SAVE EXCEPTIONS子句。不過在10g release2中可以用LOG ERRORS子句來記錄INSERT,UPDATE,MERGE,DELETE語句的錯誤記錄。

[@more@]

1.語法：
LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

INTO子句是要指定一個記錄error log的表，如不指定則會以ERR$開頭加表名的前25位字元。

simple_expression是用來方便標識錯誤的記錄的字串

REJECT LIMIT是用來限制充許最大的錯誤值，預設為0，最大值是UNLIMITED

2.error logging的限制
以下情況error logging不起作用，Oracle會自動回滾錯誤的語句：
--違反延期約束的
--直接路徑的INSERT或MERGE語句違反了唯一約束或唯一索引；
--更新操作（update or merge）違反了唯一約束或唯一索引。
另外，雖然表有LONG,LOB的表是可以用error logging特性的，但如果這些列的值是不被支援錯誤跟蹤的。

3.準備測試的指令碼
-- Create and populate a source table.
CREATE TABLE source (
id NUMBER(10) NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
CONSTRAINT source_pk PRIMARY KEY (id)
);

DECLARE
TYPE t_tab IS TABLE OF source%ROWTYPE;
l_tab t_tab := t_tab();
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description for ' || TO_CHAR(i);
END LOOP;

-- For a possible error condition.
l_tab(1000).code := NULL;
l_tab(10000).code := NULL;

FORALL i IN l_tab.first .. l_tab.last
INSERT INTO source VALUES l_tab(i);

COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);

-- Create a destination table.
CREATE TABLE dest (
id NUMBER(10) NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
CONSTRAINT dest_pk PRIMARY KEY (id)
);

-- Create a dependant of the destination table.
CREATE TABLE dest_child (
id NUMBER,
dest_id NUMBER,
CONSTRAINT child_pk PRIMARY KEY (id),
CONSTRAINT dest_child_dest_fk FOREIGN KEY (dest_id) REFERENCES dest(id)
);

建立了source表，插入了100000條記錄，但第1000和10000條的code是空值。又建立了一個父表dest，子表dest_child

4.用DBMS_ERRLOG包來建立error log的存放地

SQL> conn scott/tiger
Connected.
SQL> begin
2 dbms_errlog.create_error_log('dest');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select owner,table_name,tablespace_name from all_tables where owner='SCOTT' and table_name like

'ERR%';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT ERR$_DEST USERS

SQL> desc err$_dest;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
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)
CODE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)

系統預設建立了error log的表。

5.準備工作都已結束，現在開始DML的測試，先測試insert操作

由於DEST表的CODE欄位是有constraint的，而SOURCE表的CODE是可以為空，將SOURCE的資料都備制到DEST時肯定會出錯。

SQL> insert into dest
2 select * from source;
select * from source
*
ERROR at line 2:
ORA-01400: cannot insert NULL into ("SCOTT"."DEST"."CODE")

啟用log error則會插入符合條件的記錄
SQL> INSERT INTO dest
2 select * from source
3 log errors into err$_dest('insert1') reject limit unlimited;

99998 rows created.

SQL> COLUMN ora_err_mesg$ FORMAT A70
SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ ='insert1';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ----------------------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("SCOTT"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("SCOTT"."DEST"."CODE")

錯誤的記錄都已放在err$_dest表中。
即使insert的操作被回滾，err$_dest的表依然有資料，說明是用自制事務寫入err$_dest的。

6.update語句

修改前十條語句的code，id為9,10的兩條記錄的code改為null,這是違反約束的操作，肯定不成功。

SQL> UPDATE dest
2 SET code = DECODE(id, 9, NULL, 10, NULL, code)
3 WHERE id BETWEEN 1 AND 10;
SET code = DECODE(id, 9, NULL, 10, NULL, code)
*
ERROR at line 2:
ORA-01407: cannot update ("SCOTT"."DEST"."CODE") to NULL

啟用log error則可修改其它8條記錄。

SQL> UPDATE dest
2 SET code = DECODE(id, 9, NULL, 10, NULL, code)
3 WHERE id BETWEEN 1 AND 10
4 LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;

8 rows updated.

SQL> SELECT ora_err_number$, ora_err_mesg$
2 FROM err$_dest
3 WHERE ora_err_tag$ = 'UPDATE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ----------------------------------------------------------------------
1407 ORA-01407: cannot update ("SCOTT"."DEST"."CODE") to NULL
1407 ORA-01407: cannot update ("SCOTT"."DEST"."CODE") to NULL

7.merge語句

DELETE FROM dest
WHERE id > 50000;

MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code = b.code,
a.description = b.description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (b.id, b.code, b.description);
*
ERROR at line 9:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

和期待的一樣，加了log errors子句後已更新或插入了除了違反約束的其它記錄。
MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code = b.code,
a.description = b.description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (b.id, b.code, b.description)
LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

99998 rows merged.

SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'MERGE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

2 rows selected.

8.delete語句

dest_child表有一個外來鍵和dest相關聯，所以如果插入部分資料到dest_child，再試著刪除dest表相關聯的記錄看看。

SQL> INSERT INTO dest_child (id, dest_id) VALUES (1, 100);

1 row created.

SQL> INSERT INTO dest_child (id, dest_id) VALUES (2, 101);

1 row created.

SQL> DELETE FROM dest;
DELETE FROM dest
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.DEST_CHILD_DEST_FK) violated - child record found

加上log error子句後可以刪除其它的記錄，只保留不能刪除的記錄。

SQL> DELETE FROM dest
2 LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;

99996 rows deleted.

SQL> select count(*) from dest;

COUNT(*)
----------
2

SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'DELETE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------------------
2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
child record found

2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
child record found

9.下面開始試試error log不能工作的情況，第一種情況是開啟了約速延時

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 過程已成功完成。

下面測試一下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語句將違反結果的記錄插入

到記錄表中，這也是很容易理解的。

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

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語句對其無效也是可以理解

的。

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

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在處理的時候很可能會有所考慮。

參考文件

http://yangtingkun.itpub.net/post/468/479366