ORACLE DML Error Logging(Oracle 10g R2 New Feature)

출처 : http://www.dator.co.kr/zero/textyle/44780

데이터 이행을 하다보면 가장 힘빠지는 오류가 있다.

ORA-00001: unique constraint violated

(한글 메시지 : ORA-00001: 무결성 제약 조건에 위배됩니다)

PK 또는 Unique Index 가 생성되어 있을 때 중복된 값의 발생이 그 원인이다.

특히 As-Is 의 PK 구조와 To-Be 의 PK 구조가 다를 때와 여러 테이블을 통합하는 경우에 자주 발생한다.

몇십분 또는 몇시간 이상 SQL이 실행되다가 이 오류를 만나면 정말 황당 + 당황할 수 밖에 없다.

As-Is의 데이터가 계속 추가/변경/삭제되기 때문에, 지난 번에는 오류가 없었다고 해서 이번에 또 오류가 발생하지 말라는 보장이 없다.

데이터양이 적고 다른 이행작업에 영향이 없는 테이블인 경우는 오류를 확인하고 다시 실행하면 큰 영향이 없다.

하지만, 대용량 테이블이거나 다른 이행작업에 선행되는 테이블인 경우는 이행 전체 시간을 증가시키는 큰 위험요소가 될 수 있다.

Oracle 10g R2의 New Feature중 하나인 "DML Error Logging" 기능이 이 경우 적절한 해결책이 될 수 있다.

기본 개념은, target table에 대한 DML 실행시 발생한 오류내역과 해당 오류를 발생시킨 row들을 별도의 error logging 테이블에 저장하고 오류가 발생하지 않은 row들만 target 테이블에 저장하는 매커니즘이다.

간단한 사례로 살펴보자.

1. 준비작업

ORACLE에서 기본적으로 제공하는 HR schema의 COUNTRIES 테이블과 유사한 구조로 테스트할 테이블을 생성하고, 데이터도 채운다.

CREATE TABLE COUNTRIES
(
COUNTRY_ID    CHAR(2 BYTE) CONSTRAINT COUNTRY_ID_NN NOT NULL,
COUNTRY_NAME  VARCHAR2(40 BYTE),
CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID)
);
 
INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME)
SELECT COUNTRY_ID, COUNTRY_NAME
FROM HR.COUNTRIES;
 
COMMIT;

PK가 COUNTRY_ID 컬럼 하나로 구성되어 있다.
Index Name: COUNTRY_C_ID_PK (COUNTRY_ID)

데이터를 살펴보면,

1.SELECT COUNTRY_ID, COUNTRY_NAME
2.FROM HR.COUNTRIES;

초기 데이터는 다음과 같다.

COUNTRY_ID COUNTRY_NAME
---------- -----------------------
AR         Argentina
AU         Australia
BE         Belgium
BR         Brazil
CA         Canada
CH         Switzerland
CN         China
DE         Germany
DK         Denmark
EG         Egypt
FR         France
HK         HongKong
IL         Israel
IN         India
IT         Italy
JP         Japan
KW         Kuwait
MX         Mexico
NG         Nigeria
NL         Netherlands
SG         Singapore
UK         United Kingdom
US         United States of America
ZM         Zambia
ZW         Zimbabwe

2. 오류확인

다음과 같은 DML을 실행하면

INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME)
SELECT 'KR', 'Korea' FROM DUAL;
 
COMMIT;

아무 문제없이 잘 들어간다.

이제 일부러 오류를 발생시켜 보자.

INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME)
SELECT 'KR', 'Korea' FROM DUAL UNION ALL
SELECT 'KK', 'Test' FROM DUAL;

여지없이 오류가 발생하고, transaction은 rollback 처리된다.

1.ORA-00001: 무결성 제약 조건(COUNTRY_C_ID_PK)에 위배됩니다

3. DML Error Logging을 위한 준비

중복되는 'KR'은 별도의 오류 테이블에 저장하고 'KK'는 성공적으로 COUNTRIES 테이블에 저장하는 Error Logging을 위해서 우선 오류 테이블을 만들어야 한다.

이 작업은 DBMS_ERRLOG package를 통해 처리한다.

1.EXEC DBMS_ERRLOG.CREATE_ERROR_LOG ('COUNTRIES','ERR$COUNTRIES');

생성된 ERR$COUNTRIES 테이블의 구조는 아래와 같다.

SQL> DESC ERR$COUNTRIES
이름                                      널?      유형
----------------------------------------- -------- --------------
ORA_ERR_NUMBER$                                    NUMBER
ORA_ERR_MESG$                                      VARCHAR2(2000)
ORA_ERR_ROWID$                                     ROWID
ORA_ERR_OPTYP$                                     VARCHAR2(2)
ORA_ERR_TAG$                                       VARCHAR2(2000)
COUNTRY_ID                                         VARCHAR2(4000)
COUNTRY_NAME                                       VARCHAR2(4000)

원본 테이블의 컬럼 앞에 오류번호, 메시지, ROWID 등을 보관하기 위한 컬럼이 추가되어 있는 형태이다.

4. DML Error Logging 활용

준비는 끝났고, 아까 오류가 발생해서 전체가 ROLLBACK 되었던 SQL에 Error Logging 처리 구문을 추가하여 다시 실행해 보자.

INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME)
SELECT 'KR', 'Korea' FROM DUAL UNION ALL
SELECT 'KK', 'Test' FROM DUAL
LOG ERRORS INTO ERR$COUNTRIES
REJECT LIMIT 100;
 
1 개의 행이 만들어졌습니다.
 
COMMIT;

100개의 행까지는 ERR$COUNTRIES 테이블에 오류로 기록하고 그 이상이 되면 transaction을 rollback하도록 지시하는 샘플이다.

자세한 Syntax는 Oracle document(http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBDIGAH)를 참조하고, 개략적인 구문의 구조는 다음과 같다.

두 개의 row중 중복되지 않은 데이터만 insert에 성공하고 중복되어 오류가 발생한 데이터는 별도로 보관되었다.

정말 그런지 확인해 보자.

SELECT *
FROM COUNTRIES
WHERE COUNTRY_ID IN ('KR', 'KK');
 
CO COUNTRY_NAME
-- ------------
KK Test
KR Korea

오류 테이블을 확인해 보면

1.SELECT *
2.FROM ERR$COUNTRIES;

ORA_ERR_NUMBER$ ORA_ERR_MESG$               ORA_ERR_OPTYP$ COUNTRY_ID COUNTRY_NAME
--------------- -------------------------------------------------------------------- --------------  ----------  ------------
1    ORA-00001: 무결성 제약 조건(ZERO.COUNTRY_C_ID_PK)에 위배됩니다   I    KR   Korea

이렇게 기록된다.

문제가 발생한 데이터를 확인한 다음 업무규칙에 근거하여 처리하면 된다.

5. 고려사항

한가지 주의할 점은 Error Logging이 Autonomous Transaction(자율 트랜잭션)으로 처리된다는 점이다.

즉, LOG ERRORS INTO 구문으로 지정된 DML 문장이 성공한 다음 COMMIT 하지 않고 ROLLBACK을 한다해도 Error Logging Table의 데이터는 그대로 보존된다.

그리고, Error Logging Table도 물리적으로 존재하며 Disk 공간을 차지하므로, 데이터 유지가 필요없을 때는 truncate 해주는 작업이 필요하고 더이상 필요성이 없어졌다면 drop해 주는 걸 잊지 말자.

대용량 데이터에 대한 이행 또는 ETL 처리에도 매우 유용하게 사용될 수 있으니 상황에 맞게 적절히 활용하길 바라며...

※ 읽을 거리

1. Oracle Document: DBMS_ERRLOG

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#ARPLS680

2. INSERT Syntax :: error_logging_caluse

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBDIGAH

3. Oracle Database 10g Release 2: DBA를 위한 신기능

http://www.oracle.com/technology/global/kr/pub/articles/10gdba/nanda_10gr2dba_part1.html

김상훈 - DBA & TUNING & Developer

ORACLE DML Error Logging(Oracle 10g R2 New Feature)

티스토리툴바

ORACLE DML Error Logging(Oracle 10g R2 New Feature)

'DB/__Oracle' Related Articles

티스토리툴바