시스템 운영을 하다보면, 테이블 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 |
[스크립트]
-- [시작] 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.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 를 넣어서 실행하면 된다.
[프로그램 소스]
-- [시작]
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
;
-- [종료]
[프로그램 실행]
-- [시작]
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 로 결과가 나올 것이다.
포스팅 읽어주셔서 감사합니다. ♥ 클릭과 댓글에 의견 부탁드립니다.