시스템 운영을 하다보면, 테이블 Spec 을 변경한다던지 Object 를 수정하는 일이 종종 생긴다. 기존 컨셉대로 시스템을 사용하면 좋겠지만, 없던 프로세스가 생기던지 신사업 또는 신규 설비 추가 등 여러가지 이유로 프로그램 개선을 해야 되는 일은 자주 발생하는 일이다. Oracle 에서는 이러한 Object 에 대한 변경이 일어나면 그 Object 의 독립성(Dependency)을 체크하여 RECOMPILE 을 해주어야 한다.
특정 프로그램이 Batch 성으로 돌게 된다면, 그 프로그램 실행 직전에 RECOMPILE 프로그램을 만들어서 실행해주면 좀 더 안정적인 시스템으로 만들 수 있다. 사람이 하는 일이다보니, Object 변경 후에 RECOMPILE 을 안해줄 수도 있고, 운영에서 실행해야 할 Script 를 개발에서 실행하고 일을 마쳤다고 생각할 수도 있다. 업무 범위와 개발 범위가 명확하다면 사람이 하지 말고 프로그램에 맡기는 것이 좋다.
MSSQL 에서는 Object 를 변경하고 나서 프로그램에 대한 (VALID / INVALID) 체크를 많이 안했던 것으로 기억이 난다. 그러한 Dependency 에 대한 중요성을 간과하여 다음날 프로그램 Batch 가 돌면 에러가 자주 발생하였다. 아주 간단한 부분이고 RECOMPILE 만 해주면 되는 것인데 이 부분을 너무 쉽게 생각해서 발생한 실수이다. 개발기간이라면 중요하지 않지만, 운영에서는 한번의 에러는 곧 장애이며, 장애 등급 판정 및 보고서 작성, 그리고 재발 방지 대책까지 작성해야 하는 큰 부분이다.
1. 오라클 일괄 컴파일
Oracle 일괄 컴파일을 하기 위해서는 Object Type 은 아래의 6가지의 Object Type 을 가지고 실행하는 것이 기본이다. Object 변경 이후에 Dependency 가 Invalid 난 것을 RECOMPILE 하면 된다.
[Object Type] 1. TRIGGER 2. VIEW 3. PROCEDURE 4. FUNCTION 5. PACKAGE 6. PACKAGE BODY |
[스크립트]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- [시작] OBJECT 별 RECOMPILESELECT CASE WHEN A.OBJECT_TYPE = 'TRIGGER' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'VIEW' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'PROCEDURE' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'FUNCTION' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'PACKAGE' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'PACKAGE BODY' THEN 'ALTER PACKAGE ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE BODY' END AS DYN_SQL , A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, A.STATUS FROM ( SELECT REVERSE(FN_GET_SPLIT(REVERSE(B.OBJECT_NAME), '_', 0)) AS KEY_ID , B.* FROM ALL_SOURCE A -- SELECT * FROM ALL_SOURCE INNER JOIN ALL_OBJECTS B ON 1=1 -- SELECT * FROM ALL_OBJECTS AND B.OWNER = A.OWNER AND B.OBJECT_NAME = A.NAME WHERE 1=1 --AND UPPER(A.TEXT) LIKE '%COMPILE%' -- OBJECT 변경 AND B.OWNER IN ('INTF_MGR', 'SCM_MGR', 'SCM_ADMIN') -- DB SCHEMA 사용 AND B.OBJECT_TYPE IN ('TRIGGER', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') AND B.STATUS = 'INVALID' --AND B.OBJECT_NAME = 'SP_FP_I_PRS_WORKORDER_SORT_1021' ) A WHERE 1=1 AND REGEXP_INSTR(A.KEY_ID,'[^0-9]') > 0 GROUP BY A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, A.STATUS ORDER BY A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, A.STATUS ; -- [종료] |
2. 프로그램으로 작성
프로그램으로 만들어서 Batch 프로그램 실행 직전에 실행시키는 형태로 변경하는 것을 추천한다. 대신 에러가 발생하여도 ERROR PASS 되게 하는 것이 좋다. 아래의 프로그램은 OWNER, OBEJCT_NAME, USER_ID 3가지 파라미터를 사용하고 있다. OWNER 에는 스키마를, OBJECT_NAME 에는 변경된 OBJECT 를 넣어서 실행하면 된다.
[프로그램 소스]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | -- [시작]CREATE OR REPLACE PROCEDURE SP_PROC_PRAC_01 ( p_vOWNER IN VARCHAR2, p_vOBJECT_NAME IN VARCHAR2, p_vUSER_ID IN VARCHAR2, oFLAG OUT VARCHAR2)IS G_vOUT_FLAG VARCHAR2(1) := 'Y' -- 상태 FLAG(성공:Y, 실패) ;G_vCURR_DTTM VARCHAR2(50) ;G_vSQLERRM VARCHAR2(500) ;G_nNO NUMBER := 0 ; /* [CURSOR] Cursor Variable */ CURSOR c_DYN_SQL IS -- [시작] OBJECT 별 RECOMPILE SELECT CASE WHEN A.OBJECT_TYPE = 'TRIGGER' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'VIEW' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'PROCEDURE' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'FUNCTION' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'PACKAGE' THEN 'ALTER ' || A.OBJECT_TYPE ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE' WHEN A.OBJECT_TYPE = 'PACKAGE BODY' THEN 'ALTER PACKAGE' ||' '|| A.OWNER ||'.'|| A.OBJECT_NAME || ' COMPILE BODY' END AS DYN_SQL --, A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, A.STATUS FROM ( SELECT REVERSE(FN_GET_SPLIT(REVERSE(B.OBJECT_NAME), '_', 0)) AS KEY_ID , B.* FROM ALL_SOURCE A -- SELECT * FROM ALL_SOURCE INNER JOIN ALL_OBJECTS B ON 1=1 -- SELECT * FROM ALL_OBJECTS AND B.OWNER = A.OWNER AND B.OBJECT_NAME = A.NAME WHERE 1=1 --AND UPPER(A.TEXT) LIKE '%'||'TEMP'||'%' --AND B.OWNER = 'SCM_ADMIN' -- DB SCHEMA AND UPPER(A.TEXT) LIKE '%'||p_vOBJECT_NAME||'%' -- 변경된 객체가 포함된 OBJECT AND B.OWNER = p_vOWNER -- DB SCHEMA AND B.OBJECT_TYPE IN ('TRIGGER', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') AND B.STATUS = 'INVALID' --AND B.OBJECT_NAME = 'SP_FP_I_PRS_WORKORDER_SORT_1021' ) A WHERE 1=1 AND REGEXP_INSTR(A.KEY_ID,'[^0-9]') > 0 GROUP BY A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, A.STATUS ORDER BY A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE, A.STATUS ; -- [종료] BEGIN DBMS_OUTPUT.PUT_LINE('-----------------------------------[START]-----------------------------------'); /* CURSOR FOR IN LOOP */ FOR CUR1 IN c_DYN_SQL LOOP BEGIN DBMS_OUTPUT.PUT_LINE(CUR1.DYN_SQL); -- DYNAMIC QUERY 실행 EXECUTE IMMEDIATE CUR1.DYN_SQL;ㄷ /* CURSOR FOR IN LOOP Exception */ EXCEPTION WHEN OTHERS THEN G_vSQLERRM := SQLERRM; DBMS_OUTPUT.PUT_LINE(G_vSQLERRM); END; -- CURSOR LOOP Exception END LOOP ; DBMS_OUTPUT.PUT_LINE('-----------------------------------[END]-----------------------------------'); END;-- [종료] |
[프로그램 실행]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- [시작]DECLARE oFLAG VARCHAR2(200);BEGIN SP_PROC_PRAC_01 ( 'SCM_ADMIN' , 'TEMP' , 'admin' , oFLAG );END; -- SELECT * FROM ALL_ERRORS WHERE NAME = 'SP_PROC_PRAC_01'-- [종료] |
[결과]
결과를 보면 컴파일 오류가 발생하였다고 나오는데, 이것은 단순 OBJECT 문제가 아닌 프로그램에 오류가 있어서 나온 것이다. 정상적인 프로그램이라면 VALID 로 결과가 나올 것이다.
포스팅 읽어주셔서 감사합니다. ♥ 클릭과 댓글에 의견 부탁드립니다.