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;  


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;  


在 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. --等等............  

共有0个回答