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://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php
http://yangtingkun.itpub.net/post/468/479366

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1017980/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1017980/