Inserting Data with DML Error Logging（轉）

進行dml時，如果有任何違反約束的記錄出現，整個dml操作會終止並開始回滾，這對於大批量的資料處理，不管是從時間還是系統效能消耗上來說，都是相當大的代價。我曾經有過因為幾條資料主鍵衝突而導致幾千萬的資料匯入失敗的經歷，通常這很難讓人接受。為此，10.2版本中引入DML error logging特性，通過記錄dml執行過程中出現的異常而不回滾事務，通過延遲對異常資料的處理來保證整個事務的順利執行。
DML Error Logging 適用在INSERT, UPDATE, MERGE, and DELETE 語句。
1.建立Error Logging錯誤日誌表
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘dml_test’, ‘dml_test_error’);
2.Dml中加入Error logging子句
reject limit用來限制dml的最大報錯數，若報錯超出了最大限制數，dml語句中止並回滾，預設值是0，表示dml執行中遇到任何錯誤就終止回滾，你可以指定為unlimited。在並行dml中，reject limit設定為每個程式的最大報錯數。
SQL> insert into dml_test select t.user_id, t.username from dba_users t log ERRORS INTO dml_test_error REJECT LIMIT unlimited;

例子：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as TEST

–建立Error Logging表
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘dml_test’, ‘dml_test_error’);

Table created

–主鍵衝突，整個Insert回滾
SQL> insert into dml_test select t.user_id, t.username from dba_users t;

ORA-00001: unique constraint (SYS.PK_DML_TEST_ID) violated

–利用DML Error Logging特性，匯入成功
SQL> insert into dml_test select t.user_id, t.username from dba_users t
2 log ERRORS INTO dml_test_error REJECT LIMIT unlimited;

10 rows inserted
–檢視異常資料記錄表
SQL> select ora_err_number$ num,substr(ora_err_mesg$,1,40) as err_msg, user_id,username from dml_test_error;

NUM ERR_MSG USER_ID USERNAME
- ———————————————————
1 ORA-00001: unique constraint(SYS.PK_DML 40 ORATEXT
1 ORA-00001: unique constraint(SYS.PK_DML 39 CTXSYS
…..

SQL>
–Insert成功後，通過dml_test_error來查詢 處理失敗的記錄。

這個功能對於大批量資料Insert，Update還是很有用的。當然，也不是所有異常都可以通過DML Error Logging來處理，10.2版本只支援下列情況：
1.行值過長（Column values that are too large）
2.約束衝突（NOT NULL, unique, referential, and check constraints)
3.觸發器執行過程中出現的錯誤（Errors raised during trigger execution）
4.Partition mapping errors
5.Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)