IT技术博客

 1. DBMS_ERRLOG提供了一个存储过程，可以创建一个表的错误日志表，在对该表进行DML操作时，如果发生异常时会将该数据插入到错误日志表而不是回滚或中断

例如：

1）

05:51:32 HR@ orcl>create table t(id number primary key);

05:52:17 HR@ orcl>insert into t values (1);

05:52:25 HR@ orcl>commit;

05:52:33 HR@ orcl>insert into t values (1);

insert into t values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (HR.SYS_C0013543) violated

第二次插入1因唯一性约束发生错误。

2）使用dbms_errlog创建错误日志表

05:53:04 HR@ orcl>exec dbms_errlog.create_error_log('t'); 

备份表的名字默认是’ERR$_’+表名

05:55:27 HR@ orcl>select table_name from user_tables t where table_name like '%T'; 

TABLE_NAME 

------------------------------ 

T 

ERR$_T 

3)再次插入一个违反唯一性约束的数据

05:57:49 HR@ orcl>insert into t values (1) log errors into err$_t('tag') reject limit unlimited; 

0 rows created. 

06:04:27 HR@ orcl>select * from err$_t; 

ORA_ERR_NUMBER$ ORA_ERR_MESG$        ORA_ERR_ROWID$       ORA_ERR_OP ORA_ERR_TAG$         ID 

--------------- -------------------- -------------------- ---------- -------------------- --- 

              1 ORA-00001: unique co                      I          tag                  1 

                nstraint (HR.SYS_C00 

                13543) violated 

 

插入错误日志表启动的是自治事务，所以即使你现在回滚，错误日志的数据也会存储

2.这个用能有点像提示IGNORE_ROW_ON_DUPKEY_INDEX，但该提示只适用于INSERT插入，其它的DML语句不适用，

而错误日志的形式可以支持各种DML,下面以UPDATE为例

1)06:58:33 HR@ orcl>create table  t2(id number not null); 

06:59:17 HR@ orcl>insert into t2 values (1); 

06:59:23 HR@ orcl>commit; 

2)07:00:47 HR@ orcl>exec dbms_errlog.create_error_log('t2'); 

3) 07:02:10 HR@ orcl>update t set id=null log errors into err$_t2('update') reject limit 100; 

4) 07:02:54 HR@ orcl>select * from err$_t2; 

ORA_ERR_NUMBER$ ORA_ERR_MESG$        ORA_ERR_ROWID$       ORA_ERR_OP ORA_ERR_TAG$         ID 

--------------- -------------------- -------------------- ---------- -------------------- --- 

           1407 ORA-01407: cannot up AAATICAAEAAAA5VAAA   U          update 

                date ("HR"."T"."ID") 

                 to NULL 


           1407 ORA-01407: cannot up AAATICAAEAAAA5VAAB   U          update 

                date ("HR"."T"."ID") 

                 to NULL 

 

3.不适用条件

1)违反延迟约束

2)在直接路径下进行INSERT或MERGE操作引起的唯一性约束或索引

3)UPDATE或MERGE引起的唯一性约束或索引

例如1：以直接路径方式插入T表

07:08:47 HR@ orcl> insert /*+ APPEND*/ into   t 

07:10:04   2   (select 2 from dual) 

07:10:04   3     LOG ERRORS -- into err$_t (TO_CHAR(SYSDATE,'yyyymmddHHMISS')) 

07:10:04   4     REJECT LIMIT unlimited; 

 insert /*+ APPEND*/ into   t 

* 

ERROR at line 1: 

ORA-00001: unique constraint (HR.SYS_C0013543) violated 

 

举例二：

07:10:49 HR@ orcl>update t 

07:11:04   2     set id = 1 

07:11:04   3   where id = 2 log errors into err$_t('update') REJECT LIMIT unlimited; 

update t 

* 

ERROR at line 1: 

ORA-00001: unique constraint (HR.SYS_C0013543) violated