Oracle 中重新編譯無效的預存程序, 或函數、觸發器等對象)

Oracle 中的預存程序在有些情況下會變成失效狀態，在 PL/SQL Developer 中該預存程序的表徵圖左上方顯示一把小紅叉叉。比如儲過程所引用的對象失效，dblink 出問題啦都可能引起用到它的預存程序失效。再就我的預存程序經常會變成無效，至今原因都未查明。

查詢 dba_dependencies 視圖可以看到預存程序所引用的對象，再就在 dba_objects 視圖中可以看到對象的 created 和 last_ddl_time 時間。

上面的那種無效的儲存程，只要不是文法上有問題，重新編譯一下又是可用的了。總不能每次發現時人工去編譯的，所以要實現自動化，有以下兩種方法(網上找到的所有的 在Oracle中重新編譯所有無效的預存程序 代碼排版都很混亂，所以主要是重新整理了)：

1. Oracle SQL *Plus 中 -- 用 spool 產生指令檔，然後 @ 調入執行，代碼如下：

1. spool ExecCompProc.sql   
2.   
3. select 'alter procedure '||object_name||' compile;' from all_objects    
4. where status = 'INVALID' and object_type = 'PROCEDURE' AND owner='UNMI';    
5.   
6. spool off  
7.   
8. @ExecCompProc.sql;  

spool ExecCompProc.sql<br />select 'alter procedure '||object_name||' compile;' from all_objects<br />where status = 'INVALID' and object_type = 'PROCEDURE' AND owner='UNMI';<br />spool off<br />@ExecCompProc.sql;
2. 寫成一個預存程序 -- 讓這個預存程序在某個時機執行，比如  Job 中，代碼如下：

1. create or replace procedure compile_invalid_procedures(   
2.     p_owner varchar2 -- 所有者名稱，即 SCHEMA   
3. ) as  
4.   
5. --編譯某個使用者下的無效預存程序   
6.   
7.     str_sql varchar2(200);   
8.        
9. begin  
10.     for invalid_procedures in (select object_name from all_objects   
11.        where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))   
12.     loop   
13.         str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';   
14.         begin  
15.             execute immediate str_sql;   
16.         exception   
17.           --When Others Then Null;   
18.             when OTHERS Then  
19.                 dbms_output.put_line(sqlerrm);   
20.         end;   
21.     end loop;   
22. end;  

create or replace procedure compile_invalid_procedures(<br />p_owner varchar2 -- 所有者名稱，即 SCHEMA<br />) as<br />--編譯某個使用者下的無效預存程序<br />str_sql varchar2(200);<br />begin<br />for invalid_procedures in (select object_name from all_objects<br />where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))<br />loop<br />str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';<br />begin<br />execute immediate str_sql;<br />exception<br />--When Others Then Null;<br />when OTHERS Then<br />dbms_output.put_line(sqlerrm);<br />end;<br />end loop;<br />end;
在 SQL *Plus 中執行該預存程序時，如果要看到 dbms_output.put_line(sqlerrm); 的輸出，需要執行 set serverout on 開啟輸出。

這裡述及的是如何重新編譯預存程序，依次推及到，重新編譯 FUNCTION、PACKAGE、TYPE、TRIGGER 等，和重建 INDEX 等。所不同的就是查詢 all_objects 時的  object_type 不一樣，還有要執行的 alter 語句不一樣。

object_type 有哪些可用 select distinct object_type from all_objects 擷取到；alter 的語句寫法參考下面：

1. alter function function_name compile;   
2. alter package package)name compile;   
3. alter type type_name compile;   
4. alter index index_name rebuild;   
5. --等等............  

alter function function_name compile;<br />alter package package)name compile;<br />alter type type_name compile;<br />alter index index_name rebuild;<br />--等等............

參考：1. 為什麼我的預存程序總要重新編譯? 
        2. 在Oracle中重新編譯所有無效的預存程序

補充，請看這裡：

1. 其實預存程序、函數等是 INVALID，只要內容無錯誤就不要緊，因為執行的時候會自動重新編譯
2. 在 SQL *Plus 或者 PL/SQL Developer 的 Command Windows 中用 show  errors  procedure  procedure_name 或 show errors function function_name 可以查看到預存程序具體錯誤
3. 可以用 Oracle 提供的工具：dbms_utility.compile_schema(schema varchar2, compile_all boolean default TRUE); 來編譯某個 Schema 下的所有 PROCEDURE、FUNCTION、PACKAGE 和 TRIGGER。比如執行 dbms_utility.compile_schema('Unmi')。