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.