[转载]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 生成脚本文件，然后 @ 调入执行，代码如下：

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='TCSM';   
5.   
6. spool off  
7.   
8. @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 中，代码如下：

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(p_owner varchar2 -- 所有者名称，即 SCHEMA) as--编译某个用户下的无效存储过程str_sql varchar2(200);beginfor invalid_procedures in (select object_name from all_objectswhere status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))loopstr_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';beginexecute immediate str_sql;exception--When Others Then Null;when OTHERS Thendbms_output.put_line(sqlerrm);end;end loop;end;
在 SQL *Plus 中执行该存储过程时，如果要看到 dbms_output.put_line(sqlerrm); 的输出，需要执行 set serverout on 打开输出。

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

---

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