[转载]Oracle 中重新编译无效的存储过程

Oracle中重新编译无效的存储过程 http://www.blogjava.net/Unmi/archive/2009/01/07/250313.html
Oracle中的存储过程在有些情况下会变成失效状态，在PL/SQL Developer中该存储过程的图标左上角显示一把小红叉叉。比如储过程所引用的对象失效，dblink出问题啦都可能引起用到它的存储过程失效。再就我的存储过程经常会变成无效，至今原因都未查明。

查询 dba_dependencies视图可以看到存储过程所引用的对象，再就在dba_objects 视图中可以看到对象的 created 和last_ddl_time 时间。

上面的那种无效的存储程，只要不是语法上有问题，重新编译一下又是可用的了。总不能每次发现时人工去编译的，所以要实现自动化，有以下两种方法(网上找到的所有的在Oracle中重新编译所有无效的存储过程代码排版都很混乱，所以主要是重新整理了)：

1. Oracle SQL *Plus 中 -- 用 spool 生成脚本文件，然后 @调入执行，代码如下：

• spool ExecCompProc.sql??
  
• ??
  
• select 'alterprocedure '||object_name||' compile;'from all_objects??
  
• where status = 'INVALID'and object_type ='PROCEDURE' ANDowner='TCSM';??
  
• ??
  
• spooloff??
  
• ??
  
• @ExecCompProc.sql;??
  

spool ExecCompProc.sqlselect 'alter procedure '||object_name||'compile;' from all_objectswhere status = 'INVALID' and object_type= 'PROCEDURE' AND owner='TCSM';spool off@ExecCompProc.sql;
2. 写成一个存储过程 --让这个存储过程在某个时机执行，比如 Job中，代码如下：

• create or replaceprocedure compile_invalid_procedures(??
  
• ????p_owner varchar2 -- 所有者名称，即SCHEMA??
  
• )as??
  
• ??
  
• --编译某个用户下的无效存储过程??
  
• ??
  
• ????str_sql varchar2(200);??
  
• ??????
  
• begin??
  
• ????forinvalid_procedures in(select object_namefrom all_objects??
  
• ???????wherestatus = 'INVALID' and object_type= 'PROCEDURE' andowner=upper(p_owner))??
  
• ????loop ??
  
• ????????str_sql := 'alter procedure' ||invalid_procedures.object_name || ' compile';??
  
• ????????begin??
  
• ????????????executeimmediate str_sql;??
  
• ????????exception ??
  
• ??????????--WhenOthers Then Null;??
  
• ????????????whenOTHERSThen??
  
• ????????????????dbms_output.put_line(sqlerrm);??
  
• ????????end;??
  
• ????endloop; ??
  
• end;??
  

create or replace procedure compile_invalid_procedures(p_ownervarchar2 -- 所有者名称，即 SCHEMA)as--编译某个用户下的无效存储过程str_sqlvarchar2(200);beginfor invalid_procedures in (select object_namefrom all_objectswhere status = 'INVALID' and object_type ='PROCEDURE' and owner=upper(p_owner))loopstr_sql := 'alterprocedure ' ||invalid_procedures.object_name || 'compile';beginexecute immediate str_sql;exception--When Others ThenNull;when OTHERS Thendbms_output.put_line(sqlerrm);end;endloop;end;
在 SQL *Plus 中执行该存储过程时，如果要看到dbms_output.put_line(sqlerrm); 的输出，需要执行 setserverout on 打开输出。

参考：1. 为什么我的存储过程总要重新编译?
???????2. 在Oracle中重新编译所有无效的存储过程

[版权声明]本站内文章，如未标注[转载]，均系原创或翻译之作，本人 Unmi保留一切权利。本站原创及译作未经本人许可，不得用于商业用途及传统媒体。网络媒体可随意转载，或以此为基础进行演译，但务必以链接形式注明原始出处和作者信息，否则属于侵权行为。另对本站转载他处文章，俱有说明，如有侵权请联系本人，本人将会在第一时间删除侵权文章。及此说明，重之之重。