ORACLE編譯失效物件小結

strivechao發表於2021-09-02
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;
 

下文大多參考博文 ,修改並作了總結、整理

 

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

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://jzhil2004.blog.163.com/blog/static/275585042010117113214172/

http://blog.csdn.net/tianlesoftware/article/details/4843600

https://www.cnblogs.com/kerrycode/p/3723715.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25469263/viewspace-2789976/，如需轉載，請註明出處，否則將追究法律責任。

相關文章