一、查詢無效對象的方法：
1.統計失效對象
select owner, object_type, status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type
2.檢視具體失效對象
col owner for a20;
col object_name for a32;
col object_type for a16
col status for a8
select owner, object_name, object_type, status
from dba_objects
where status='INVALID'
order by 1, 2,3;
column owner format a15
column object_name format a25
column object_type format a15
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status != 'VALID'
AND owner != 'SYS'
AND owner != 'SYSTEM';
3.檢視失效對象的依賴關系
col owner for a30
col name for a30
col type for a18
col referenced_owner for a20
col referenced_name for a30
col referenced_type for a18
col dependency_type for a4
set line 300
select OWNER,NAME,TYPE,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE,DEPENDENCY_TYPE from dba_dependencies where OWNER='SCOTT' AND NAME='V_TEST';
二、編譯無效對象的方法：
1.使用ALTER * COMPLIE語句手工進行編譯，這個适用于少數、個别對象失效
1)生成手動編譯無效對象腳本
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects where status = 'INVALID' and object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW','SYNONYM','JAVA SOURCE','JAVA CLASS');
2)手動執行
alter package . compile;
alter package . compile body;
alter view . compile;
alter trigger compile;
2.執行@$ORACLE_HOME/rdbms/admin/utlrp.sql腳本編譯資料庫失效對象。
許多情況下，由于資料庫的更新或遷移，會導緻資料庫中的對象失效。由于對象之間可能存在複雜的倚賴關系，是以手工編譯通常無法順利通過。通常我們會在Oracle的更新指導中看到這個腳本，Oracle強烈推薦在migration/upgrade/downgrade之後，通過運作此腳本編譯失效對象。但是注意,Oracle提醒，此腳本需要用SQLPLUS以SYSDBA身份運作，并且當時資料庫中最好不要有活動事物或DDL操作，否則極容易導緻死鎖的出現。
另外，utlrp.sql 裡面其實調用了$ORACLE_HOME/rdbms/admin/utlrcmp.sql來編譯失效對象https://www.cndba.cn/leo1990/article/3180https://www.cndba.cn/leo1990/article/3180https://www.cndba.cn/leo1990/article/3180
3.ORACLE提供了自動編譯的接口dbms_utility.compile_schema(user,false); 調用這個過程就會編譯所有失效的過程、函數、觸發器、包
exec dbms_utility.compile_schema( 'SCOTT' );
4.在SQL*plus中利用中間腳本編譯
https://www.cndba.cn/leo1990/article/3180
set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects
where status = 'INVALID'
and OWNER = 'APPS'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE','TRIGGER','JAVA SOURCE','JAVA CLASS','VIEW','SYNONYM');
select 'alter package ' || owner || '.' || object_name ||
' compile body;'
from dba_objects
where status = 'INVALID'
and OWNER = 'APPS'
and object_type in ('PACKAGE BODY');
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid
2)在SQL*Plus中執行
格式：@檔案所在本地路徑/檔案名
注：路徑不能有中文
SQL>@/home/oracle/reCompile.sql;
備注：運作腳本reCompile.sql的時候，會建立另一個腳本run_invalid，緊跟着執行該腳本，完成編譯工作
https://www.cndba.cn/leo1990/article/3180https://www.cndba.cn/leo1990/article/3180
5.編寫PL/SQL利用遊标編譯
DECLARE
v_objname user_objects.object_name%TYPE;
v_objtype user_objects.object_type%TYPE;
CURSOR cur IS
SELECT object_name,object_type
FROM USER_OBJECTS
WHERE status = 'INVALID'
AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW','SYNONYM','JAVA SOURCE','JAVA CLASS');
BEGIN
OPEN cur;
LOOP
FETCH cur into v_objname, v_objtype;
EXIT WHEN cur%NOTFOUND;
BEGIN
EXECUTE Immediate 'alter ' || v_objtype || ' ' || v_objname||' Compile';
dbms_output.put_line('編譯' || v_objtype || ' ' || v_objname || '()成功');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('編譯' || v_objtype ||' ' || v_objname || '()失敗.' || SQLERRM);
END;
END LOOP;
CLOSE cur;
END;
/
注意：視圖，存儲過程，函數、包等，如果代碼本身沒有什麼錯誤，隻是引用的對象發生了變化。也會失效。但并不影響調用，因為ORACLE在調用時會自動重新編譯的，如果其它對象變化後導緻編譯有錯誤。這時調用時重新編譯後也是錯誤并處于失效狀态，是以調用會出錯。
https://www.cndba.cn/leo1990/article/3180
https://www.cndba.cn/leo1990/article/3180
https://www.cndba.cn/leo1990/article/3180
版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。