ORACLE編譯失效對象

在日常資料庫維護過程中，我們會探索資料庫中一些對象（包Package、預存程序Procedure、函數Function、視圖View、同義字.....）會失效,呈現無效狀態（INVALID）。有時候需要定期檢查資料庫中存在哪些失效對象，對於存在異常的對象需要重新編譯，有些自動失效的對象，一般會在下次調用的時候，會被重新編譯，所以這些不需要人工幹預。那麼為什麼對象突然會失效呢？又如何快速、高效的編譯失效對象呢？哪些失效的對象不需要我們去重新編譯呢？

資料庫物件失效原因

資料庫物件失效的原因很多，下面大致歸納了一些常見的原因（有些漏掉的，希望大家補充）：

1: 當被引用對象的結構變更時，都會使得相關的依賴對象轉變為INVALID狀態。

資料庫中的對象（預存程序，函數，包，視圖，觸發器），它們往往需要直接或者間接的引用其它對象，對象的依賴包括直接和間接二種，其中直接依賴是指儲存物件直接依賴於被引用對象，而間接依賴是指對象間接依賴於被引用對象

要查看被引用的對象，可以通過下面SQL查看

select * from dba_dependencies where name='&objectname';

select * from all_dependencies where name='&objectname';

select * from user_dependencies where name='&objectname';

舉個簡單例子，視圖V_TEST引用了表TEST，TEST表修改了表結構時，會導致視圖V_TEST變為無效對象。

SQL> CREATE TABLE TEST ( ID NUMBER(10));

Table created.

SQL> CREATE VIEW V_TEST AS SELECT * FROM TEST;

View created.

SQL> SELECT OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='V_TEST';

OBJECT_NAME STATUS

------------------- ----------------

V_TEST VALID

--修改表結構，增加一個欄位NAME後，視圖V_TEST變為無效

SQL> ALTER TABLE TEST ADD NAME VARCHAR(12);

Table altered.

SQL> SELECT OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='V_TEST';

OBJECT_NAME STATUS

------------------- ----------------

V_TEST INVALID

--查詢檢視V_TEST後，資料庫會重新編譯視圖

SQL> SELECT * FROM V_TEST;

no rows selected

SQL> SELECT OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='V_TEST';

OBJECT_NAME STATUS

------------------- ----------------

V_TEST VALID

其實不管視圖，像預存程序，函數、包等，如果代碼本身沒有什麼錯誤，只是引用的對象發生了變化。也會失效。但並不影響調用，因為ORACLE在調用時會自動重新編譯的，如果其它對象變化後導致編譯有錯誤。這時調用時重新編譯後也是錯誤並處於失效狀態，所以調用會出錯。

2：發布SQL指令碼時（包、預存程序、函數等），沒有充分測試，編譯時間出錯，這時對象變為無效。

3: 資料庫升級、遷移時，出現大量無效對象(本質原因，個人臆測歸結為原因1)。

4： 諸如此類各種情況：例如，Oracle 會自動維護分區索引，對於全域索引，如果在對分區表操作時，沒有指定update index，則會導致全域索引失效，需要重建。

編譯失效對象的方法

統計失效的對象：

select owner, object_type, status, count(*)

from dba_objects

where status='INVALID'

group by owner, object_type, status

order by owner, object_type

查看具體失效對象

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;

下文大多參考博文Oracle中編譯無效的對象常用方法,修改並作了總結、整理

1:   使用ALTER *** COMPLIE語句手工進行編譯，這個適用於少數、個別對象失效

alter package <schema name>.<package_name> compile;

alter package <schema name>.<package_name> compile body;

alter view <schema name>.<view_name> compile;

alter trigger <schema).<trigger_name> compile;

2：執行@$ORACLE_HOME/rdbms/admin/utlrp.sql指令碼編譯資料庫失效對象。

許多情況下，由於資料庫的升級或遷移，會導致資料庫中的對象失效。由於對象之間可能存在複雜的倚賴關係，所以手工編譯通常無法順利通過。通常我們會在Oracle的升級指導中看到這個指令碼，Oracle強烈推薦在migration/upgrade/downgrade之後，通過運行此指令碼編譯失效對象。但是注意,Oracle提醒，此指令碼需要用SQLPLUS以SYSDBA身份運行，並且當時資料庫中最好不要有活動事物或DDL操作，否則極容易導致死結的出現（這是很容易理解的）。

Oracle highly recommends running this script towards the end of of any migration/upgrade/downgrade.

另外，utlrp.sql 裡面其實調用了$ORACLE_HOME/rdbms/admin/utlrcmp.sql來編譯失效對象。

3：ORACLE提供了自動編譯的介面dbms_utility.compile_schema(user,false); 調用這個過程就會編譯所有失效的過程、函數、觸發器、包

exec dbms_utility.compile_schema( 'SCOTT' )

4:  一些網友書寫的編譯失效對象（經過整理）

SQL 1: 編譯失效對象

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

object_type in ('PACKAGE','FUNCTION','PROCEDURE','TRIGGER','JAVA SOURCE','JAVA CLASS','')

;

spool off;

set heading on;

set feedback on;

set echo on;

@run_invalid.sql

本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

SQL 2:在上面的方法中，只能知道某某編譯失敗，不清楚失敗原因，可以用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 ('FUNCTION','JAVA SOURCE','JAVA CLASS','PROCEDURE','PACKAGE','TRIGGER');

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;

出處：http://www.cnblogs.com/kerrycode/