Oracle10g新特性——LOG ERRORS 记录DML错误日志

以下转自： http://blog.itpub.net/4227/viewspace-558234/  作者： yangtingkun ----------------------------------------------------------------------------------------------------------------------------- 当一个DML运行的时候，如果遇到了错误，则这条语句会整个回滚，就好像没有执行过。不过对于一个大的DML而言，如果个别数据错误而导致整个语句的回滚，会浪费很多的资源和运行时间，从10g开始Oracle支持记录DML语句的错误，而允许语句自动继续执行。 第一篇、这篇介绍DML记录语句的用法。 看一个插入语句的简单例子： SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(10)); 表已创建。 SQL> INSERT INTO T VALUES (1, 'A'); 已创建 1 行。 SQL> INSERT INTO T VALUES (1, '1234567890A'); INSERT INTO T VALUES (1, '1234567890A') * 第 1 行出现错误: ORA-12899: 列 "YANGTK"."T"."NAME" 的值太大 (实际值: 11, 最大值: 10) 可以看到，由于插入的列超过了字段定义的长度，导致了Oracle报错。 下面创建记录DML错误信息的记录表，通过DBMS_ERRLOG包来进行创建，而这个包目前只包括这一个过程： SQL> DESC DBMS_ERRLOG PROCEDURE CREATE_ERROR_LOG 参数名称                                    类型                            输入/输出默认值? -------------------------         --------------                     ---- -------- DML_TABLE_NAME                 VARCHAR2                   IN ERR_LOG_TABLE_NAME          VARCHAR2                   IN DEFAULT ERR_LOG_TABLE_OWNER        VARCHAR2                  IN DEFAULT ERR_LOG_TABLE_SPACE           VARCHAR2                 IN DEFAULT SKIP_UNSUPPORTED                BOOLEAN                   IN DEFAULT 利用CREATE_ERROR_LOG来创建T表的DML错误记录表： 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) Oracle创建的错误记录表包括错误号码，错误信息，记录的ROWID信息，错误操作类型，错误标签，以及表中对应的列。 下面利用包含LOG ERROR语句的INSERT语句再次插入数据： SQL> INSERT INTO T VALUES (1, '1234567890A') 2 LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT 1; 已创建0行。 SQL> COL ID FORMAT A5 SQL> COL NAME FORMAT A12 SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_OPTYP$, ID, NAME 2 FROM T_ERROR_LOG; ORA_ERR_NUMBER$ OR ID NAME --------------- -- ----- ------------ 12899 I 1 1234567890A SQL> SELECT ORA_ERR_MESG$ 2 FROM T_ERROR_LOG; ORA_ERR_MESG$ --------------------------------------------------------------------------- ORA-12899: 列 "YANGTK"."T"."NAME" 的值太大 (实际值: 11, 最大值: 10) 可以看到，插入成功执行，但是插入记录为0条。从对应的错误信息表中已经包含了插入的信息。而且从错误信息表中还可以看到对应的错误号和详细错误信息。 下面详细介绍一下LOG ERRORS的语法，INTO语句后面跟随的就是指定的错误记录表的表名。 在INTO语句后面，可以跟随一个表达式，用来设置本次语句执行的错误在错误记录表中对应的TAG： SQL> INSERT INTO T VALUES (2, '1234567890A') 2 LOG ERRORS INTO T_ERROR_LOG ('TEST') REJECT LIMIT 1; 已创建0行。 SQL> COL ORA_ERR_TAG$ FORMAT A12 SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_OPTYP$, ORA_ERR_TAG$, ID, NAME 2 FROM T_ERROR_LOG 3 WHERE ORA_ERR_TAG$ = 'TEST'; ORA_ERR_NUMBER$ OR ORA_ERR_TAG$ ID NAME --------------- -- ------------ ----- ------------ 12899 I TEST 2 1234567890A 有了这个语句，就可以很轻易的在错误记录表中找到某次操作所对应的所有的错误，这对于错误记录表中包含了大量数据，且本次语句产生了多条错误信息的情况十分有帮助。只要这个表达式的值可以转化为字符串类型就可以： SQL> INSERT INTO T VALUES (2, '1234567890A') 2 LOG ERRORS INTO T_ERROR_LOG (0) REJECT LIMIT 1; 已创建0行。 SQL> INSERT INTO T VALUES (2, '1234567890A') 2 LOG ERRORS INTO T_ERROR_LOG (TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')) 3 REJECT LIMIT 1; 已创建0行。 而REJECT LIMIT则限制语句出错的数量。 SQL> SELECT LENGTH(TNAME), COUNT(*) FROM TAB 2 GROUP BY LENGTH(TNAME) 3 ORDER BY 1; LENGTH(TNAME) COUNT(*) ------------- ---------- 1 1 3 2 4 2 5 6 6 6 7 2 8 4 10 4 11 4 12 4 14 1 17 1 18 1 20 1 已选择14行。 从查询结果中可以看到，表名长度超过10的记录有12个，如果将表名作为NAME列的数值，插入到T表中，将有12条记录无法成功插入： SQL> INSERT INTO T 2 SELECT ROWNUM, TNAME 3 FROM TAB 4 LOG ERRORS INTO T_ERROR_LOG 5 ('REJECT10') 6 REJECT LIMIT 10; SELECT ROWNUM, TNAME * 第 2 行出现错误: ORA-12899: 列 "YANGTK"."T"."NAME" 的值太大 (实际值: 17, 最大值: 10) SQL> INSERT INTO T 2 SELECT ROWNUM, TNAME 3 FROM TAB 4 LOG ERRORS INTO T_ERROR_LOG 5 REJECT LIMIT 12; 已创建27行。 SQL> SELECT COUNT(*) FROM T_ERROR_LOG 2 WHERE ORA_ERR_TAG$ = 'REJECT10'; COUNT(*) ---------- 11 可以看到，当设置的REJECT LIMIT的值小于出错记录数时，语句会报错，这时LOG ERRORS语句没有起到应有的作用，插入语句仍然以报错结束。而如果将REJECT LIMIT的限制设置大于等于出错的记录数，则插入语句就会执行成功。而所有出错的信息都会存储到LOG ERROR对应的表中。 只要指定了LOG ERRORS语句，不管最终插入语句十分成功的执行完成，在错误记录表中都会记录语句执行过程中遇到的错误。比如第一个插入由于出错数目超过REJECT LIMIT的限制，这时在记录表中会存在REJECT LIMIT + 1条记录数，因此这条记录错误导致了整个SQL语句的报错。 如果不管碰到多少错误，都希望语句能继续执行，则可以设置REJECT LIMIT为UNLIMITED： SQL> INSERT INTO T 2 SELECT ROWNUM, TNAME 3 FROM TAB 4 LOG ERRORS INTO T_ERROR_LOG 5 REJECT LIMIT UNLIMITED; 已创建27行。 SQL> ROLLBACK; 回退已完成。 SQL> SELECT COUNT(*) FROM T_ERROR_LOG; COUNT(*) ---------- 62 如果执行了回滚操作，可以看到，T_ERROR_LOG表中的记录并不会减少，这说明Oracle是利用自治事务的方式插入错误记录表的。 第二篇 上一篇简单介绍了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在处理的时候很可能会有所考虑。 第三篇 除了上一篇文章介绍的不支持的操作外，DML记录错误语句还有一些不支持的数据类型，比如：LONG、LONG RAW、BLOG、CLOB、NCLOB、BFILE以及各种对象类型。 Oracle不支持这些类型的原因也很简单，这些特殊的类型不是包含了大量的记录，就是需要通过特殊的方法来读取，因此Oracle没有办法在SQL处理的时候将对应列的信息写到错误记录表中。 SQL> DROP TABLE T PURGE; 表已删除。 SQL> DROP TABLE T_ERROR_LOG PURGE; 表已删除。 SQL> CREATE TABLE T 2 (ID NUMBER, 3 NAME VARCHAR2(30), 4 RESUME CLOB); 表已创建。 SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG') BEGIN DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG'); END; * 第 1 行出现错误: ORA-20069: Unsupported column type(s) found: RESUME ORA-06512: 在 "SYS.DBMS_ERRLOG", line 233 ORA-06512: 在 line 1 可以看到，由于T表拥有不支持的列，导致创建错误记录表的过程报错，错误提示就是T表中包含了不支持的列。 如果手工添加CLOB字段到错误记录表： SQL> ALTER TABLE T DROP (RESUME); 表已更改。 SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG') PL/SQL 过程已成功完成。 SQL> ALTER TABLE T ADD RESUME CLOB; 表已更改。 SQL> ALTER TABLE T_ERROR_LOG ADD RESUME CLOB; 表已更改。 下面创始执行DML语句： SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST') 2 LOG ERRORS INTO T_ERROR_LOG 3 REJECT LIMIT 1; LOG ERRORS INTO T_ERROR_LOG * 第 2 行出现错误: ORA-38904: LOB 列 "RESUME" 不支持 DML 错误事件记录 SQL> INSERT INTO T VALUES (1, 'A', 'TEST') 2 LOG ERRORS INTO T_ERROR_LOG 3 REJECT LIMIT 1; LOG ERRORS INTO T_ERROR_LOG * 第 2 行出现错误: ORA-38904: LOB 列 "RESUME" 不支持 DML 错误事件记录 可以看到，Oracle会直接报错。而且第二个INSERT语句的执行也会报错，说明Oracle是在执行之前检查了错误记录表的数据类型，而不是在执行的时候才去处理。 即使Oracle的DML并不包含不支持列的数据，Oracle也会报错： SQL> INSERT INTO T VALUES (1, 'A', 'TEST'); 已创建 1 行。 SQL> UPDATE T SET NAME = 'B' 2 WHERE ID = 1 3 LOG ERRORS INTO T_ERROR_LOG 4 REJECT LIMIT 1; LOG ERRORS INTO T_ERROR_LOG * 第 3 行出现错误: ORA-38904: LOB 列 "RESUME" 不支持 DML 错误事件记录 下面创始删除T_ERROR_LOG表中的RESUME列： SQL> ALTER TABLE T_ERROR_LOG DROP (RESUME); 表已更改。 SQL> UPDATE T SET NAME = 'B' 2 WHERE ID = 1 3 LOG ERRORS INTO T_ERROR_LOG 4 REJECT LIMIT 1; 已更新 1 行。 SQL> UPDATE T SET NAME = LPAD('A', 31, 'A') 2 WHERE ID = 1 3 LOG ERRORS INTO T_ERROR_LOG 4 REJECT LIMIT 1; 已更新0行。 SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST') 2 LOG ERRORS INTO T_ERROR_LOG 3 REJECT LIMIT 1; 已创建0行。 SQL> SELECT COUNT(*) FROM T_ERROR_LOG; COUNT(*) ---------- 2 可以看到，删除错误记录语句所不支持的列后，LOG ERRORS语句反而可以顺利执行，而且无论DML语句是否包括哪些不支持列的数据。 不过，对于一个已经在使用的表，是不可能为了添加错误记录表而删除不支持的列，而后在添加回来的，这没有关系，完全可以通过其他表中间表来实现： SQL> DROP TABLE T_ERROR_LOG PURGE; 表已删除。 SQL> CREATE TABLE T_INTER AS SELECT ID, NAME FROM T; 表已创建。 SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T_INTER', 'T_ERROR_LOG') PL/SQL 过程已成功完成。 SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST') 2 LOG ERRORS INTO T_ERROR_LOG 3 REJECT LIMIT 1; 已创建0行。 可以看到，虽然CREATE_ERROR_LOG过程在创建T_ERROR_LOG表时指定的是T_INTER表，但是这个过程实际上只是根据源表来获取列的信息，而并没有在源表和错误记录表之间建立任何的关系，所以只要列满足条件，任何途径创建的错误记录表都可以使用。 错误记录表还可以手工创建： SQL> CREATE TABLE T_ERROR_LOG1 AS SELECT * FROM T_ERROR_LOG 2 WHERE 1 = 0; 表已创建。 SQL> DROP TABLE T_ERROR_LOG PURGE; 表已删除。 SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST') 2 LOG ERRORS INTO T_ERROR_LOG1 3 REJECT LIMIT 1; 已创建0行。