DML過程中記錄錯誤記錄檔，dml記錄錯誤記錄檔

DML過程中記錄錯誤記錄檔，dml記錄錯誤記錄檔

  當你插入幾百萬資料時，因為有幾條髒資料而導致插入失敗，是不是非常惱火。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.違反唯一鍵或約束的更新運算會導致失敗復原。