DML Error Logging 기능의 활용방안

1.목적

-대용량 배치DML(INSERT, UPDATE,DELETE,MERGE) 실행시에 99% 가 완료되고 1% 남았을때 ORA-XXXX 에러에 의해서 전체건이 Rollback 되어버린다.
-대용량 배치는 시간에 매우예민한데 몇시간 걸리는 작업이 에러 몇건 때문에 전체가 Rollback 되면 손실이 크지않을수 없다.
-Oracle 10g R2 부터 정상적인 건들은 Commit 되고 에러난 건들만 나중에 따로 처리할 수 있는 문법이 추가 되었다.
-따라서 트랜잭션은 All Or Nothing 이란말은 옛말이 되어버렸다.

2.DML Error Logging 기능의 사용방법

1) Error Logging 테이블 생성
-타겟테이블의 Layout
CREATE TABLE raises
(emp_id NUMBER, sal NUMBER
CONSTRAINT check_sal CHECK(sal > 8000));

먼저 DBMS_ERRLOG 패키지의 EXECUTE 권한이 있어야 하고 Create Table 권한도 있어야 한다.
아래 예제를 실행시키면 Error Logging 테이블이 자동으로 생성된다.

예제)
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(’raises’, ’errlog’);

위의 예제는 raises 테이블이 INSERT, UPDATE,DELETE 가 일어나는 Target 테이블이고
Error Logging 테이블명은 errlog 이다.

2) DML을 수행하면서 Error Logging 수행
아래 예제를 수행시키면서 Error Logging 테이블에서 나중에 작업을 식별해내기위해
‘my_bad’라는 식별자를 사용했음.

예제)
INSERT INTO raises
SELECT employee_id, salary*1.1 FROM employees
WHERE commission_pct > 0.2
LOG ERRORS INTO errlog (’my_bad’) REJECT LIMIT 10 ; -- 에러가 10건이 넘으면 rollback 됨

SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;

ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL
--------------------------- -------------------- ------ -------
ORA-02290: check constraint my_bad 161 7700

3)주의사항
-REJECT LIMIT 절은 UNLIMITED 로하는것이 권장사항임. -->무한대로 Error Logging 할수 있음.
-Parallel DML 수행시는 REJECT LIMIT 10 라고 설정했을경우 각 Process 마다 10건씩 Logging 함.


3. DML Error Logging 의 가능한 상황 및 제약사항

1) Error Logging 가능한 상황
-컬럼 길이가 너무클때
-컬럼제약사항 : (NOT NULL, unique, referential, and check constraints)
-트리거에서 에러가 날때
-데이터 타입 변환에 실패 했을때
-파티션 매핑에 실패했을경우
-MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

2)제약사항 : 아래의 3가지경우는 Error Logging 이 안됨.
– Violated deferred constraints. (기본값이 NOT DEFERRABLE 이므로 이럴일은 거의 없음)
- Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
(Parallel 이나 direct path Insert 시에 unique 인덱스의 Dup 에러는 체크하지 않음.
Update는 Parallel 이나 direct path 와 상관없이 unique 체크안함)
-You cannot track errors in the error logging table for LONG, LOB, or object type columns.
(LONG, LOB 타입은 에러체크를 하지않음)

4.Reference From Oracle Corp
1) Administrator's Guide10g Release 2 (10.2) :15-9
2) SQL Reference 10g Release 2 (10.2) : 18-62

5.참조사항 -Error Logging 테이블의 Layout