Oracle 中重新编译无效的存储过程, 或函数、触发器等对象

我会带着你远行 2022-08-27 13:55 146阅读 0赞

Oracle 中的存储过程在有些情况下会变成失效状态，在 PL/SQL Developer 中该存储过程的图标左上角显示一把小红叉叉。比如储过程所引用的对象失效，dblink 出问题啦都可能引起用到它的存储过程失效。再就我的存储过程经常会变成无效，至今原因都未查明。  
  
查询 dba\_dependencies 视图可以看到存储过程所引用的对象，再就在 dba\_objects 视图中可以看到对象的 created 和 last\_ddl\_time 时间。  
  
上面的那种无效的存储程，只要不是语法上有问题，重新编译一下又是可用的了。总不能每次发现时人工去编译的，所以要实现自动化，有以下两种方法(网上找到的所有的 [在Oracle中重新编译所有无效的存储过程][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 owner='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 owner=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. [为什么我的存储过程总要重新编译?][Link 1]   
        2. [在Oracle中重新编译所有无效的存储过程][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')。  
  
参考：1. [为什么我创建的存储过程,存储函数都是无效的?][Link 2]  
        2. [DBMS\_UTILITY][DBMS_UTILITY]

[Oracle]: http://www.xland.com.cn/article/74/77/0803/28375.htm
[Link 1]: http://www.lslnet.com/linux/dosc1/10/linux-156591.htm
[Link 2]: http://topic.csdn.net/t/20020821/11/957708.html
[DBMS_UTILITY]: http://stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10802/d_util.htm#1002333