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

congdepeng

浏览: 120781 次
性别:
来自: 苏州

最近访客更多访客>>

Peak_

zhangwenli_bj

flyer999

mywebuser

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Oracle SQL 脚本 .net

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

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

上面的那种无效的存储程，只要不是语法上有问题，重新编译一下又是可用的了。总不能每次发现时人工去编译的，所以要实现自动化，有以下两种方法(网上找到的所有的在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='TCSM'; ??
??
spool off??
??
@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 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;??

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中重新编译所有无效的存储过程

分享到：