Tibero 전체 오브젝트 컴파일 프로시저

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 ;
/