首页 » ORACLE » DML error capture (捕获DML错误日志)

DML error capture (捕获DML错误日志)

今天发现用dbms_errlog可以捕获错误日志,很给力,直接看实验

<--转载请声明出处 www.anbob.com zhangweizhao>
[oracle@orazhang ~]$ sqlplus anbob/anbob

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 10 17:19:22 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

anbob@ORCL> create table t1(id number primary key);

Table created.

anbob@ORCL> create table t2(id number primary key);

Table created.
anbob@ORCL> INSERT INTO t1 VALUES(1);

1 row created.

anbob@ORCL> INSERT INTO t1 VALUES(2);

1 row created.

anbob@ORCL> INSERT INTO t1 VALUES(3);

1 row created.

anbob@ORCL> INSERT INTO t1 VALUES(4);

1 row created.

anbob@ORCL> INSERT INTO t2 VALUES(1);

1 row created.

anbob@ORCL> INSERT INTO t2 VALUES(2);

1 row created.

anbob@ORCL> INSERT INTO t2 VALUES(3);

1 row created.

anbob@ORCL> INSERT INTO t2 VALUES(8);

1 row created.

anbob@ORCL> INSERT INTO t2 VALUES(9);

1 row created.

anbob@ORCL> COMMIT;

anbob@ORCL> execute dbms_errlog.create_error_log('T1');

PL/SQL procedure successfully completed.

--和物化视图一样会多出一个日志表
anbob@ORCL> desc dbms_errlog
PROCEDURE CREATE_ERROR_LOG
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 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

anbob@ORCL> select * from err$_t1;

no rows selected

anbob@ORCL> insert into t1 select * from t2;
insert into t1 select * from t2
*
ERROR at line 1:
ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated

anbob@ORCL> select * from err$_t1;

no rows selected

anbob@ORCL> insert into t1 select * from t2 log errors reject limit unlimited;

2 rows created.
anbob@ORCL> select * from err$_t1;
ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                                    ORA_ERR_RO OR ORA_ERR_TA ID
--------------- -------------------------------------------------------------------------------- ---------- -- ---------- ----------
              1 ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated                                  I             1
              1 ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated                                  I             2
              1 ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated                                  I             3

anbob@ORCL> insert into t1 values('a') log errors reject limit unlimited;

0 rows created.

anbob@ORCL> select * from err$_t1;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                                    ORA_ERR_RO OR ORA_ERR_TA ID
--------------- -------------------------------------------------------------------------------- ---------- -- ---------- ----------
              1 ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated                                  I             1
              1 ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated                                  I             2
              1 ORA-00001: unique constraint (ANBOB.SYS_C0013041) violated                                  I             3
           1722 ORA-01722: invalid number                                                                   I             a

anbob@ORCL> drop table err$_T1;

Table dropped.
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Abel Jennette | #1
    2011-12-21 at 07:16

    Hey there! Quick question that’s completely off topic. Do you know how to make your site mobile friendly? My website looks weird when viewing from my iphone. I’m trying to find a template or plugin that might be able to resolve this problem. If you have any recommendations, please share. With thanks!