dbms_errlog让DML操作记录日志

Oracle的SQL loader工具，可以记录错误日志，并限制错误记录数。Oracle在进行DML操作时使用dbms_errlog可达到同样的效果。

看一下测试纪录

 

1、首先创建一张数据源表
SQL>  create table t4 as select object_id from dba_objects;

Table created.

SQL> desc t4
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER

2、检查数据源数据
SQL> select max(object_id) from t4;

MAX(OBJECT_ID)
--------------
         53071

SQL> select min(object_id) from t4;

MIN(OBJECT_ID)
--------------
             2
SQL> select count(*) from t4;

  COUNT(*)
----------
     50615            
3、创建目标表，并修改结构
SQL> create table t5 as select * from t4 where 1=2;

Table created.

SQL> alter table t5 modify (object_id number(3));

Table altered.

4、对目标表进行常规插入测试
SQL> insert into t5 select * from t4;
insert into t5 select * from t4
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

5、使用dbms_errlog结合reject limit进行测试
SQL> exec dbms_errlog.create_error_log('t5','t5_errors');

PL/SQL procedure successfully completed.

SQL> insert into t5 select * from t4 log errors into t5_errors reject limit unlimited;

953 rows created.

SQL> select count(*) from t5;

  COUNT(*)
----------
       953

SQL> select count(*) from t5_errors;

  COUNT(*)
----------
     49662
    
SQL> rollback;

Rollback complete.

SQL> select count(*) from t5;

  COUNT(*)
----------
         0

SQL> select count(*) from t5_errors;

  COUNT(*)
----------
     49662
回滚操作不会影响错误日志的生成。

SQL> desc t5_errors
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 OBJECT_ID                                          VARCHAR2(4000)

dbms_errlog会在参照表t5的基础上新建立一个表，并增加5个字段，记录DML错误日志。