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在处理的时候很可能会有所考虑。