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

<--转载请声明出处 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.