1.查找无效存储过程
SELECT OBJECT_NAME,TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS'),STATUS FROM USER_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE IN('FUNCTION','PROCEDURE','PACKAGE') ;
2.重新编译
- alter object_type object_namecompile;
上述两步总结如下
- declare
- v1 varchar2(500);
- begin
- for k in(select object_type,object_namefrom user_objects
- where status='INVALID'and object_type in('FUNCTION','PROCEDURE','PACKAGE')) loop
- v1:='alter '||k.object_type||' '||k.object_name||' compile';
- execute immediate v1;
- end loop;
- end;
3.编译无效存储过程的过程
create or replace procedure compile_invalid_procedures(
) as
--编译某个用户下的无效存储过程
end;