"INVALID 오브젝트"의 두 판 사이의 차이
DB CAFE
(새 문서: == INVAILD 오브젝트 COMPILE 처리 == <source lang=sql> SELECT DISTINCT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'|| OBJECT_NAME || ' COMPILE ;' AS COMP_COMMAND FROM DBA_OBJECTS WHER...) |
(→INVAILD 오브젝트 COMPILE 처리) |
||
(같은 사용자의 중간 판 2개는 보이지 않습니다) | |||
2번째 줄: | 2번째 줄: | ||
<source lang=sql> | <source lang=sql> | ||
SELECT DISTINCT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'|| OBJECT_NAME || ' COMPILE ;' AS COMP_COMMAND | SELECT DISTINCT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'|| OBJECT_NAME || ' COMPILE ;' AS COMP_COMMAND | ||
− | FROM DBA_OBJECTS | + | FROM DBA_OBJECTS |
− | WHERE STATUS = 'INVALID' | + | WHERE STATUS = 'INVALID' |
− | + | AND OBJECT_TYPE NOT IN ( 'PACKAGE BODY','SYNONYM') | |
− | UNION | + | UNION |
+ | |||
-- 패키지 바디만 | -- 패키지 바디만 | ||
SELECT DISTINCT 'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE BODY;' AS COMP_COMMAND | SELECT DISTINCT 'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE BODY;' AS COMP_COMMAND | ||
14번째 줄: | 15번째 줄: | ||
; | ; | ||
</source> | </source> | ||
+ | |||
+ | == INVALID 오브젝트 관리 프로시져 == | ||
+ | <source lang=sql> | ||
+ | CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_COMPILE_INVAILD_OBJECT | ||
+ | ( | ||
+ | -- OBJECT_OWNER IN VARCHAR2 DEFAULT NULL, -- OBJECT OWNER | ||
+ | p_obj_nm IN VARCHAR2 DEFAULT NULL, -- OBJECT NAME | ||
+ | p_obj_ty IN VARCHAR2 DEFAULT 'SYNONYM', -- OBJECT NAME | ||
+ | p_exec in number default 1 -- 0 :SQL만 출력 , 1:실행 | ||
+ | ) -- authid current_user | ||
+ | IS | ||
+ | CURSOR INVALID_OBJECT IS | ||
+ | SELECT DISTINCT OWNER,OBJECT_TYPE,OBJECT_NAME | ||
+ | FROM DBA_OBJECTS | ||
+ | WHERE STATUS = 'INVALID' | ||
+ | AND OBJECT_TYPE IN (p_obj_ty) | ||
+ | AND OBJECT_NAME = p_obj_nm | ||
+ | AND OWNER NOT IN ('ERPAPP','ERPHR','PUBLIC','MIGCOMMON','MIGBASEDATA'); | ||
+ | |||
+ | |||
+ | V_SQL VARCHAR2(5000); | ||
+ | V_MSG long; | ||
+ | |||
+ | BEGIN | ||
+ | DBMS_OUTPUT.ENABLE; | ||
+ | FOR V_ROW IN INVALID_OBJECT | ||
+ | LOOP | ||
+ | BEGIN | ||
+ | DBMS_OUTPUT.ENABLE(4000); | ||
+ | |||
+ | -- 바뀐 유저로 권한 주기 | ||
+ | V_SQL := 'ALTER '||V_ROW.OBJECT_TYPE||' '||V_ROW.OWNER||'.'|| V_ROW.OBJECT_NAME || ' COMPILE'; | ||
+ | IF p_exec <> 0 THEN | ||
+ | EXECUTE IMMEDIATE V_SQL; | ||
+ | DBMS_OUTPUT.PUT_LINE('SUCCESS VALID: '||V_SQL); | ||
+ | ELSE | ||
+ | DBMS_OUTPUT.PUT_LINE('SQL : '||V_SQL); | ||
+ | END IF; | ||
+ | EXCEPTION | ||
+ | WHEN OTHERS THEN | ||
+ | DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' ||V_SQL||'-'|| SQLERRM); | ||
+ | END; | ||
+ | END LOOP; | ||
+ | |||
+ | EXCEPTION | ||
+ | WHEN OTHERS THEN | ||
+ | DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); | ||
+ | |||
+ | END; | ||
+ | / | ||
+ | </source> | ||
+ | |||
+ | [[Category:oracle]] |
2020년 4월 17일 (금) 11:02 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 INVAILD 오브젝트 COMPILE 처리[편집]
SELECT DISTINCT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'|| OBJECT_NAME || ' COMPILE ;' AS COMP_COMMAND
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE NOT IN ( 'PACKAGE BODY','SYNONYM')
UNION
-- 패키지 바디만
SELECT DISTINCT 'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE BODY;' AS COMP_COMMAND
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ( 'PACKAGE BODY')
order by 1
;
2 INVALID 오브젝트 관리 프로시져[편집]
CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_COMPILE_INVAILD_OBJECT
(
-- OBJECT_OWNER IN VARCHAR2 DEFAULT NULL, -- OBJECT OWNER
p_obj_nm IN VARCHAR2 DEFAULT NULL, -- OBJECT NAME
p_obj_ty IN VARCHAR2 DEFAULT 'SYNONYM', -- OBJECT NAME
p_exec in number default 1 -- 0 :SQL만 출력 , 1:실행
) -- authid current_user
IS
CURSOR INVALID_OBJECT IS
SELECT DISTINCT OWNER,OBJECT_TYPE,OBJECT_NAME
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN (p_obj_ty)
AND OBJECT_NAME = p_obj_nm
AND OWNER NOT IN ('ERPAPP','ERPHR','PUBLIC','MIGCOMMON','MIGBASEDATA');
V_SQL VARCHAR2(5000);
V_MSG long;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR V_ROW IN INVALID_OBJECT
LOOP
BEGIN
DBMS_OUTPUT.ENABLE(4000);
-- 바뀐 유저로 권한 주기
V_SQL := 'ALTER '||V_ROW.OBJECT_TYPE||' '||V_ROW.OWNER||'.'|| V_ROW.OBJECT_NAME || ' COMPILE';
IF p_exec <> 0 THEN
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('SUCCESS VALID: '||V_SQL);
ELSE
DBMS_OUTPUT.PUT_LINE('SQL : '||V_SQL);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' ||V_SQL||'-'|| SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/