728x90
반응형
Tibero에 제공하는 패키지에서는 Public 컴파일을 안해주고 있어서 수작업으로 프로시저를 만들었고
해당 프로시저는 스키마별 인벨리드 전/후 건수 확인 하도록 만듬.
select * from dba_objects
where 1=1
and status='INVALID'
and OBJECT_TYPE not in('TABLE','INDEX','LOB')
order by OWNER,OBJECT_NAME
;
위 쿼리로 확인 후 아래 프로시저 호출 하면됨
call PL_INVALID_ALL_COMPILE;
CREATE OR REPLACE PROCEDURE SYS.PL_INVALID_ALL_COMPILE
IS
V_OWNER DBA_OBJECTS.OWNER%TYPE;
V_OBJECT_TYPE DBA_OBJECTS.OBJECT_TYPE%TYPE;
V_CNT NUMBER;
V_SYNONYM VARCHAR2(4000);
CURSOR OBJECT_INVALID_CUR
IS
SELECT OWNER,OBJECT_TYPE,COUNT(*) CNT
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
and OBJECT_TYPE not in('TABLE','INDEX','LOB')
GROUP BY OWNER,OBJECT_TYPE ;
CURSOR SYNONYM_REPLACE_CUR
IS
SELECT 'CREATE OR REPLACE PUBLIC SYNONYM '||SYNONYM_NAME||' FOR '||ORG_OBJECT_OWNER||'.'||ORG_OBJECT_NAME||';'
FROM DBA_SYNONYMS
WHERE (OWNER,SYNONYM_NAME) IN (SELECT OWNER, OBJECT_NAME
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OWNER='PUBLIC'
AND OBJECT_TYPE='SYNONYM');
type OBJECT_CNT_CUR_TYPE is REF CURSOR;
OBJECT_CNT_CUR OBJECT_CNT_CUR_TYPE;
V_CNT_SQL VARCHAR2(1000);
BEGIN
dbms_output.enable('10000000000');
OPEN OBJECT_INVALID_CUR;
LOOP
FETCH OBJECT_INVALID_CUR INTO V_OWNER,V_OBJECT_TYPE,V_CNT;
--데이터가 없으면 중지
if OBJECT_INVALID_CUR%notfound then
DBMS_OUTPUT.PUT_LINE('OBJECT COMPILE COMPLET');
EXIT;
else
--1. INVAILD 건수 확인
DBMS_OUTPUT.PUT_LINE('컴파일 전 :'||V_OWNER||' '||V_OBJECT_TYPE||': '||V_CNT);
--2. 스키마 별 컴파일
V_CNT_SQL :='SELECT OWNER,OBJECT_TYPE,COUNT(*) FROM DBA_OBJECTS WHERE STATUS=''INVALID'' AND OWNER=:a GROUP BY OWNER,OBJECT_TYPE' ;
OPEN OBJECT_CNT_CUR FOR V_CNT_SQL USING V_OWNER;
LOOP
UTL_RECOMP.RECOMP_SERIAL(''||V_OWNER||'');
FETCH OBJECT_CNT_CUR INTO V_OWNER,V_OBJECT_TYPE,V_CNT;
EXIT WHEN OBJECT_CNT_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('컴파일 후 :'||V_OWNER||' '||V_OBJECT_TYPE||': '||V_CNT);
END LOOP;
--3. 컴파일 후 INVAILD 건수 확인
CLOSE OBJECT_CNT_CUR;
end if;
END LOOP;
CLOSE OBJECT_INVALID_CUR;
OPEN SYNONYM_REPLACE_CUR;
LOOP
FETCH SYNONYM_REPLACE_CUR INTO V_SYNONYM;
EXIT WHEN SYNONYM_REPLACE_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_SYNONYM);
--1. 시노님 생성 스크립트 다이나믹쿼리로 수행
EXECUTE IMMEDIATE V_SYNONYM;
SELECT COUNT(*)
INTO V_CNT
FROM DBA_SYNONYMS
WHERE (OWNER,SYNONYM_NAME) IN (SELECT OWNER, OBJECT_NAME
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OWNER='PUBLIC'
AND OBJECT_TYPE='SYNONYM');
DBMS_OUTPUT.PUT_LINE('재성성 후 : '||V_CNT);
END LOOP;
CLOSE SYNONYM_REPLACE_CUR;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('오류가 발생했다');
DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_backtrace);
END ;
/728x90
반응형
'DBMS > Tibero' 카테고리의 다른 글
| Tibero Trial 라이선스 발급 및 Tibero 6 EOS/EOL 안내 (0) | 2025.10.24 |
|---|---|
| Tibero JOB HISTORY 확인 방법 (0) | 2025.08.20 |
| Tibero 세션 Lock 확인 및 관련 세션 과 SQL확인 방법 (3) | 2025.08.05 |
| Tibero JOB등록 할때 참고 하시요 (0) | 2025.07.04 |
| Oracle TIbero 스케줄러 JOB 시간 등록 (0) | 2023.07.03 |