Oracle中的存储过程在有些情况下会变成失效状态,在PL/SQL Developer中该存储过程的图标左上角显示一把小红叉叉。比如储过程所引用的对象失效,dblink出问题啦都可能引起用到它的存储过程失效。再就我的存储过程经常会变成无效,至今原因都未查明。查询dba_dependencies视图可以看到存储过程所引用的对象,再就在dba_objects视图中可以看到对象的created和last_ddl_time时间。上面的那种无效的存储程,只要不是语法上有问题,重新编译一下又是可用的了。总不能每次发现时人工去编译的,所以要实现自动化,有以下两种方法(网上找到的所有的在Oracle中重新编译所有无效的存储过程代码排版都很混乱,所以主要是重新整理了):1. Oracle SQL *Plus中——用spool生成脚本文件,然后@调入执行,代码如下:spool ExecCompProc.sqlselect ’alter
procedure ’||object_name||’ compile;’ from all_objectswhere 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);beginfor invalid_procedures in
(select object_name from all_objectswhere status = ’INVALID’ and
object_type = ’PROCEDURE’ and wner=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打开输出。这里述及的是如何重新编译存储过程,依次推及到,重新编译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’)。