DML过程中记录错误日志

灰太狼 2021-04-27 15:52 428阅读 0赞

当你插入几百万数据时，由于有几条脏数据而导致插入失败，是不是很恼火。10g R2之后有个新功能。将插入过程中失败的记录插入到还有一张表中。
````
SQL> drop table test purge;

SQL> drop table test_bad purge;  
SQL> create table test as select * from dba_objects where 1<>1;

SQL> execute dbms_errlog.create_error_log('test','test_bad');
````
````
create table TEST  
(  
  OWNER          VARCHAR2(30),  
  OBJECT_NAME    VARCHAR2(128),  
  SUBOBJECT_NAME VARCHAR2(30),  
  OBJECT_ID      NUMBER,  
  DATA_OBJECT_ID NUMBER,  
  OBJECT_TYPE    VARCHAR2(19),  
  CREATED        DATE,  
  LAST_DDL_TIME  DATE,  
  TIMESTAMP      VARCHAR2(19),  
  STATUS         VARCHAR2(7),  
  TEMPORARY      VARCHAR2(1),  
  GENERATED      VARCHAR2(1),  
  SECONDARY      VARCHAR2(1),  
  NAMESPACE      NUMBER,  
  EDITION_NAME   VARCHAR2(30)  
);  
````
````
create table TEST_BAD  
(  
  ORA_ERR_NUMBER$ NUMBER,  
  ORA_ERR_MESG$   VARCHAR2(2000),  
  ORA_ERR_ROWID$  UROWID(4000),  
  ORA_ERR_OPTYP$  VARCHAR2(2),  
  ORA_ERR_TAG$    VARCHAR2(2000),  
  OWNER           VARCHAR2(4000),  
  OBJECT_NAME     VARCHAR2(4000),  
  SUBOBJECT_NAME  VARCHAR2(4000),  
  OBJECT_ID       VARCHAR2(4000),  
  DATA_OBJECT_ID  VARCHAR2(4000),  
  OBJECT_TYPE     VARCHAR2(4000),  
  CREATED         VARCHAR2(4000),  
  LAST_DDL_TIME   VARCHAR2(4000),  
  TIMESTAMP       VARCHAR2(4000),  
  STATUS          VARCHAR2(4000),  
  TEMPORARY       VARCHAR2(4000),  
  GENERATED       VARCHAR2(4000),  
  SECONDARY       VARCHAR2(4000),  
  NAMESPACE       VARCHAR2(4000),  
  EDITION_NAME    VARCHAR2(4000)  
);  
````
````
SQL> insert into test(owner) values(lpad('1',31,'aa'))  
    log errors into test_bad;  
insert into test(owner) values(lpad('1',31,'aa'))  
                               *  
第 1 行出现错误:  
ORA-12899: 列 "LCAM_TEST"."TEST"."OWNER" 的值太大 (实际值: 31, 最大值: 30)  
  
  
SQL> insert into test(owner) values(lpad('2',30,'bb'))  
    log errors into test_bad;  
````
已创建 1 行。

````
SQL> col ORA_ERR_NUMBER format a8;  
SQL> col ORA_ERR_MESG$ format a50;  
SQL> col OWNER format a20;  
SQL> select to_char(ORA_ERR_NUMBER$) as ORA_ERR_NUMBER, ORA_ERR_MESG$, OWNER from test_bad;  
ORA_ERR_ ORA_ERR_MESG$                                      OWNER  
-------- -------------------------------------------------- --------------------  
12899    ORA-12899: 列 "LCAM_TEST"."TEST"."OWNER" 的值太大  aaaaaaaaaaaaaaaaaaaa  
         (实际值: 31, 最大值: 30)                           aaaaaaaaaa1  
SQL> select count(1) from test;  
  
  
  COUNT(1)  
----------  
         1  
           
SQL> drop table test1 purge;  
SQL> drop table test_bad1 purge;  
SQL> create table test1 as select * from dba_objects where 1<>1;  
SQL> alter table test1 modify object_id number(2);  
SQL> execute dbms_errlog.create_error_log('test1','test_bad1');  
SQL> insert into test1 select * from dba_objects  
    log errors into test_bad1;  
insert into test1 select * from dba_objects  
                         *  
第 1 行出现错误:  
ORA-01438: 值大于为此列指定的同意精度  
SQL> select count(1) from test1;  
  COUNT(1)  
----------  
         0  
SQL> select count(1) from dba_objects where length(object_id)<=2 or object_id is null;  
  COUNT(1)  
----------  
`````

106

**--能够看到。上面的语句一有错误就回滚。须要写出下列语句**

SQL> insert into test1 select * from dba_objects  
    log errors into test_bad1  
    reject limit unlimited;  
已创建106行。  
SQL> select count(1) from test1;  
  COUNT(1)  
----------  
       106  
SQL> select count(1) from dba_objects where length(object_id)<=2 or object_id is null;  
  COUNT(1)  
----------

106

须要说明的是：

1.插入到错误日志是自治事务，不会影响主事务。  
2.使用log error并不会使append失效，但写error不会使用直接路径插入。  
3.违反唯一键或约束的更新运算会导致失败回滚。