ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle10g新特性——记录DML错误日志(一)

Oracle10g新特性——记录DML错误日志(一)

原创 Linux操作系统 作者:yangtingkun 时间:2009-03-01 23:40:02 0 删除 编辑

当一个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 LIMITUNLIMITED

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是利用自治事务的方式插入错误记录表的。

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10455371