ITPub博客

首页 > 数据库 > Oracle > Oracle10g新特性——LOG ERRORS 记录DML错误日志

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

Oracle 作者:拖延者患者小彬彬 时间:2018-11-19 18:08:37 0 删除 编辑

以下转自: 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行。

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

请登录后发表评论 登录
全部评论

注册时间:2016-10-14

  • 博文量
    5
  • 访问量
    5882