Oracle認證 ：Oracle中重新編譯無效的儲存過程,或函式、觸發器

 
 
 
　Oracle 中的儲存過程在有些情況下會變成失效狀態，在 PL/SQL Developer 中該儲存過程的圖示左上角顯示一把小紅叉叉。比如儲過程所引用的物件失效，dblink 出問題啦都可能引起用到它的儲存過程失效。再就我的儲存過程經常會變成無效，至今原因都未查明。
　　查詢 dba_dependencies 檢視可以看到儲存過程所引用的物件，再就在 dba_objects 檢視中可以看到物件的 created 和 last_ddl_time 時間。
　　上面的那種無效的儲存程，只要不是語法上有問題，重新編譯一下又是可用的了。總不能每次發現時人工去編譯的，所以要實現自動化，有以下兩種方法（網上找到的所有的 在Oracle中重新編譯所有無效的儲存過程 程式碼排版都很混亂，所以主要是重新整理了）：
　　1. Oracle SQL *Plus 中 —— 用 spool 生成指令碼檔案，然後 @ 調入執行，程式碼如下：
　　spool ExecCompProc.sql
　　select ’alter procedure ’||object_name||’ compile;’ from all_objects
　　where status = ’INVALID’ and object_type = ’PROCEDURE’ AND wner=’UNMI’;
　　spool off
　　@ExecCompProc.sql;
　　2. 寫成一個儲存過程 —— 讓這個儲存過程在某個時機執行，比如Job 中，程式碼如下：
　　create or replace procedure compile_invalid_procedures(
　　p_owner varchar2 -- 所有者名稱，即 SCHEMA
　　) as
　　--編譯某個使用者下的無效儲存過程
　　str_sql varchar2(200);
　　begin
　　for invalid_procedures in (select object_name from all_objects
　　where status = ’INVALID’ and object_type = ’PROCEDURE’ and wner=upper(p_owner))
　　loop
　　str_sql := ’alter procedure ’ ||invalid_procedures.object_name || ’ compile’;
　　begin
　　execute immediate str_sql;
　　exception
　　--When Others Then Null;
　　when OTHERS Then
　　dbms_output.put_line(sqlerrm);
　　end;
　　end loop;
　　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 的語句寫法參考下面：
　　alter function function_name compile;
　　alter package package)name compile;
　　alter type type_name compile;
　　alter index index_name rebuild;
　　--等等............
　　補充，請看這裡：
　　1. 其實儲存過程、函式等是 INVALID，只要內容無錯誤就不要緊，因為執行的時候會自動重新編譯
　　2. 在 SQL *Plus 或者 PL/SQL Developer 的 Command Windows 中用 show errorsprocedureprocedure_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’）。