DML错误日志表

前天写了篇文章《》，提到了存在唯一约束前提下，重复数据的问题。

很感谢建荣兄，他给我补充了两点，

1. 冲突数据也可以考虑通过errorlog的方式，可以很快定位。
2. 对于含有null的复合索引，mysql和oracle的结果完全不同。

对于第二点，前同事曌哥，碰巧也说到了，MySQL下和Oracle的些许不同，这块我需要验证下，才能体会得更清楚些。

对于第一点，之前没用过，借着这次机会，学习一下。

我们先直接看使用过程。首先测试表有两条数据，并且创建(a, b, c)的唯一约束，

SQL select * from test;     ID A          B      C ---------- ---------- ---------- ----------      1 a          a      a      2 b          b      b

SQL alter table test add constraint unq_test_01 unique(a, b, c); Table altered.

接着，使用DBMS_ERRLOG包的create_error_log存储过程，指定需要创建ERROR LOG的表，

SQL exec dbms_errlog.create_error_log(dml_table_name='TEST'); PL/SQL procedure successfully completed.

此时会新增一张名为ERR$_TEST的表，可以看出，除了TEST表原有的ID、A、B和C字段外，还有另外五个ORA_ERR_开始的字段，

此时我们向TEST表插入一条重复的数据，自然会报错，违反唯一性约束的错误，

SQL insert into test values(3, 'a', 'a', 'a'); insert into test values(3, 'a', 'a', 'a') * ERROR at line 1: ORA-00001: unique constraint (BISAL.UNQ_TEST_01) violated

我们增加log errors子句，再次执行，

SQL insert into test values(3, 'a', 'a', 'a') log errors into err$_test ('manual_load') reject limit 100; 0 rows created.

此时未报错，TEST表没有新增数据，

SQL select * from test;     ID A          B      C ---------- ---------- ---------- ----------      1 a          a      a      2 b          b      b

ERR$_TEST表则增加了一条数据，从ID、A、B和C可以看出，就是刚才要插入的重复数据，换句话说，这条不可能插入TEST表的数据，插入了ERR$_TEST表，另外ORA_ERR_MESG$字段显示的错误信息，正是不加log errors子句时，控制台直接返回的错误信息，我们猜出ORA_ERR_OPTYP$字段是I表示的是INSERT，插入操作，

从上面的过程，可以了解ERROR LOG的基本用途，即可以存储一些操作原表数据错误的记录，一方面不会让原表操作报错，另一方面会自动记录这些错误，便于检索。

接下来我们看看，Oracle官方的介绍，从《Oracle® Database PL/SQL Packages and Types Reference》文档可以检索DBMS_ERRLOG包。

DBMS_ERRLOG包可以创建一张错误日志表，当执行一些DML操作碰见错误的时候，可以让这些操作继续执行，而不是自动终止和回滚，这样可以节省执行时间，以及系统资源，

The  DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources.

可以使用DBMS_ERRLOG包前提是，用户有EXECUTE包的权限，并且需要拥有SELECT基表或视图的权限，以及CREATE TABLE全县，当然表空间要有QUOTA配额，

Security on this package can be controlled by granting  EXECUTE on this package to selected users or roles. The  EXECUTE privilege is granted publicly. However, to create an error logging table, you need  SELECT access on the base table or view, the  CREATE  TABLE privilege, as well as tablespace quota for the target tablespace.

DBMS_ERRLOG包中只有一个存储过程CREATE_ERROR_LOG，作用就是，创建记录发生DML错误的日志表。错误日志支持INSERT, UPDATE, MERGE, and DELETE这些操作。

不支持以下数据类型，即对以下类型字段执行DML，不会记录日志表，

LONG, CLOB, BLOB, BFILE, and ADT

这是CREATE_ERROR_LOG的语法，

这是参数说明，

dml_table_name

The name of the DML table to base the error logging table on. The name can be fully qualified (for example,  emp,  scott.emp,  "EMP",  "SCOTT"."EMP"). If a name component is enclosed in double quotes, it will not be upper cased.

err_log_table_name

The name of the error logging table you will create.

The default is the first 25 characters in the name of the DML table prefixed with  'ERR$_'. Examples are the following:

dml_table_name:  'EMP',  err_log_table_name:  'ERR$_EMP'

dml_table_name:  '"Emp2"',  err_log_table_name:  'ERR$_Emp2'

err_log_table_owner

The name of the owner of the error logging table. You can specify the owner in  dml_table_name. Otherwise, the schema of the current connected user is used.

err_log_table_space

The tablespace the error logging table will be created in. If not specified, the default tablespace for the user owning the DML error logging table will be used.

skip_unsupported

When set to  TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table.

When set to  FALSE, an unsupported column type will cause the procedure to terminate.

The default is  FALSE.

dml_table_name表示需要监控的表，如上面实验TEST表。

err_log_table_name表示需要新建的日志表，默认采用监控表的前25个字符，加上ERR$前缀，如上面实验ERR$_TEST表。

err_log_table_owner表示监控表dml_table_name拥有者，默认当前用户。

err_log_table_space表示日志表所在表空间，默认为和dml_table_name存储于相同的表空间。

skip_unsupported表示若碰见，上述不支持的数据类型，选择中止执行，还是仅略过这些错误，默认FALSE。

从上述实验，我们可以看出，日志表会有五个ORA_ERR_开始的字段，

Column NameData TypeDescription|------

ORA_ERR_NUMBER$

NUMBER

Oracle error number

ORA_ERR_MESG$

VARCHAR2(2000)

Oracle error message text

ORA_ERR_ROWID$

ROWID

Rowid of the row in error (for update and delete)

ORA_ERR_OPTYP$

VARCHAR2(2)

Type of operation: insert ( I), update ( U), delete ( D)

Note: Errors from the update clause and insert clause of a  MERGE operation are distinguished by the  U and  I values.

ORA_ERR_TAG$

VARCHAR2(2000)

Value of the tag supplied by the user in the error logging clause

当然可以不用CREATE_ERROR_LOG自动建日志表，手工根据上述字段要求，同样可以创建日志表，使用log errors子句操作，需要注意的是，以上这些字段，可以不要求顺序，但必须是这张表的前几个字段，否则就会报错。

我们看下会报什么错误，首先需要获取ERR$_TEST创建语句，

SQL set long 1000

SQL select dbms_metadata.get_ddl('TABLE','ERR$_TEST') FROM dual; DBMS_METADATA.GET_DDL('TABLE','ERR$_TEST') --------------------------------------------------------------------------------

CREATE TABLE "BISAL"."ERR$_TEST"    (    "ORA_ERR_NUMBER$" NUMBER,     "ORA_ERR_MESG$" VARCHAR2(2000),     "ORA_ERR_ROWID$" UROWID (4000),     "ORA_ERR_OPTYP$" VARCHAR2(2),     "ORA_ERR_TAG$" VARCHAR2(2000),     "ID" VARCHAR2(4000),     "A" VARCHAR2(4000),     "B" VARCHAR2(4000),     "C" VARCHAR2(4000)    ) **SEGMENT CREATION IMMEDIATE**   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1  MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS H_CACHE DEFAULT)   TABLESPACE "USERS"

注意此处指定了SEGMENT CREATION IMMEDIATE，即禁用了11g默认的新特性，延迟段创建，建表同时，分配段空间。另外，NUMBER类型的ID列，此处变为了VARCHAR2类型，其他VARCHAR2类型的字段，长度变为了4000。

手工创建ERR$_TEST表，ORA_ERR_开始的字段，放置末尾，

CREATE TABLE ERR$_TEST (

    ID VARCHAR2(4000),     A VARCHAR2(4000),     B VARCHAR2(4000),     C VARCHAR2(4000),

    ORA_ERR_NUMBER$ NUMBER,     ORA_ERR_MESG$ VARCHAR2(2000),     ORA_ERR_ROWID$ UROWID (4000),     ORA_ERR_OPTYP$ VARCHAR2(2),     ORA_ERR_TAG$ VARCHAR2(2000));

Table created.

执行重复数据的插入，报错ORA_ERR_MESG$字段必须是前五个字段，

SQL insert into test values(3, 'a', 'a', 'a') log errors into err$_test ('manual_load') reject limit 100; insert into test values(3, 'a', 'a', 'a') log errors into err$_test ('manual_load') reject limit 100                                                           * ERROR at line 1: ORA-38901: column "ORA_ERR_MESG$" of table "ERR$_TEST" must be one of the first "5" columns

我们根据提示，将ORA_ERR_MESG$放置前五，

CREATE TABLE ERR$_TEST (
    ORA_ERR_MESG$ VARCHAR2(2000),

    ID VARCHAR2(4000),     A VARCHAR2(4000),     B VARCHAR2(4000),     C VARCHAR2(4000),

    ORA_ERR_NUMBER$ NUMBER,     ORA_ERR_ROWID$ UROWID (4000),     ORA_ERR_OPTYP$ VARCHAR2(2),     ORA_ERR_TAG$ VARCHAR2(2000));

Table created.

执行报错，说明确实这五个字段，需要放置前五，

SQL insert into test values(3, 'a', 'a', 'a') log errors into err$_test ('manual_load') reject limit 100; insert into test values(3, 'a', 'a', 'a') log errors into err$_test ('manual_load') reject limit 100                                                           * ERROR at line 1: ORA-38901: column "ORA_ERR_NUMBER$" of table "ERR$_TEST" must be one of the first "5" columns

再看一下log errors子句，“manual_load”是一个标签，要求数字或字符类型，辅助定位。另一个可选参数，是reject limit，定义了INSERT操作报错前，日志表记录最大值，可以设置为UNLIMITED，默认只是0，意思是碰见第一个错误，就记录日志表，并且回滚语句。

我们执行log errors子句，此时出现错误，即使执行rollback，TEST表和ERR$_TEST表数据不会回滚，有可能

SQL insert into test values(3, 'a', 'a', 'a') log errors into err$_test ('manual_load') reject limit 100; 0 rows created.

SQL rollback; Rollback complete.

SQL select count(*) from test;   COUNT(*) ----------      2

SQL select count(*) from err$_test;   COUNT(*) ----------      1

错误日志可以记录如下，DML操作错误，

• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints)
• Errors raised during trigger execution
• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors
• Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

Errors resulting from type conversion between a column in a subquery and the corresponding column of the table

以下操作错误，不会记录日志，

• Violated deferred constraints.
• Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
• Any update operation UPDATE or MERGE that raises a unique constraint or index violation.

总结：

1. 错误日志表，可以记录DML一些操作错误，当然有一些限制。

2. 错误日志表，可以使用DBMS_ERRLOG包自动创建，也可以手工创建，但要求五个ORA_ERR_字段必须位于表定义前列，和原表相比，NUMBER类型变为VARCHAR2(4000)，所有VARCHAR2类型均变为4000长度定义。

3. 错误日志表，有些数据类型不支持，可以使用标签，以及reject limit设置一些错误记录的属性。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)