Oracle10g DML錯誤日誌表

當insert 語句中使用子查詢插入資料的時候（例如insert into a  select * from b），如果發生錯誤，insert語句會被中斷，且整個事務會被回滾，如果子查詢返回1000條，當在第501條時遇到錯誤。這個時候前面插入的500條資料都被回滾，既浪費時間，又浪費資源。下面將討論如何避免這種情況的發生。

 

為了使用DML記錄錯誤日誌（DML錯誤日誌表），需要在DML語句後面加一個語句，加上後DML引起的錯誤會被記錄到一張特殊的表裡（此表需要單獨建立）。此時，DML引起的異常 將不會導致所有的操作全部回滾，oracle將略過錯誤，繼續執行這個DML.

 

DML錯誤日誌表對於insert ,update,merge,delete都起作用。這裡以insert 為例。

 

SQL> select * from t1;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 develop                      China
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

SQL> select * from t2;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 develop                      China
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO

SQL> truncate table t2;

表被截斷。

SQL> select * from t2;

未選定行

SQL> alter table t2 add constraint pk_t2 primary key (deptno);

表已更改。

SQL> insert into t2 select * from t1;
insert into t2 select * from t1
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (TEST.PK_T2)


SQL> select * from t2;

未選定行

 

可見插入的時候由於主鍵不能重複，導致插入出現異常，現在一條資料也沒插進去，呵呵，白費力氣。

接下來討論如何使用 LOG ERRORS 來避免這種情況：

 

1.建立DML錯誤日誌表，利用oracle自帶的包 裡面有一個儲存過程 可以拿來直接呼叫，含有2個引數，第一個：需要記錄DML錯誤日誌的表名（此例為T2），第二個：DML錯誤日誌表的名字（此例為ERR_EMPL）。 

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('T2', 'ERR_EMPL');

PL/SQL 過程已成功完成。

     

2.插入資料

 

SQL> insert into t2 select * from t1
  2  LOG ERRORS INTO err_empl ('t2_load') REJECT LIMIT 25;

已建立4行。

SQL> select * from t2;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 develop                      China
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO

 

可見資料插入成功了，只有主鍵重複的那一行記錄沒有插入成功。REJECT LIMIT 25的意思是如果錯誤數超過25 ，將中斷此DML，回滾操作。

 

我們再來看看 記錄錯誤日誌的那張表裡面都記錄些什麼：

SQL> set linesize 500;
SQL> col DNAME format a10;
SQL> col DEPTNO format a10;
SQL> COL ORA_ERR_TAG$ format a5;
SQL> COL ORA_ERR_TAG$ format a10;
SQL> col LOC format a10;
SQL> col ORA_ERR_MESG$  format a20;
SQL> col ORA_ERR_ROWID$  format a10;
SQL> select * from err_empl
SQL> /

ORA_ERR_NUMBER$ ORA_ERR_MESG$        ORA_ERR_RO ORA_ ORA_ERR_TA DEPTNO     DNAME      LOC
--------------- -------------------- ---------- ---- ---------- ---------- ---------- ----------
              1 ORA-00001: 違反唯一             I    t2_load    40         OPERATIONS BOSTON
                約束條件 (TEST.PK_T2
                )

可以看出，此表記錄了該DML執行 期間遇到的所有的錯誤。資料之所以在遇到錯誤後仍能夠插入成功，歸功於這張表。

 

下面是關於DML錯誤日誌表column的一些描述：

 

Column Name	Data Type	Description
ORA_ERR_NUMBER$	NUMBER	Oracle error number
ORA_ERR_MESG$	VARCHAR2(2000)	Oracle error message text
ORA_ERR_ROWID$	ROWID	Rowid of the row in error (for update and delete)
ORA_ERR_OPTYP$	VARCHAR2(2)	Type of operation: insert (I), update (U), delete (D) Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.
ORA_ERR_TAG$	VARCHAR2(2000)	Value of the tag supplied by the user in the error logging clause

 

上述column的含義很容易看懂吧。

 

記得這個實用的DML錯誤日誌表，PS：此乃10G新特性，10G之前的版本不能使用。