INVALID 오브젝트
DB CAFE
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;
/