如何重新编译存储过程等

工作中，存储过程、函数等难免失效，那么怎么找到失效的存储过程呢

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') ;

重新编译对象的语法

alter object_type object_name compile;

当然，上面两个步骤也可以通过一条语句完成

declare
v1 varchar2(500);
begin
for k in (select object_type,object_name from 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;

 

查出无效的存储过程并编译

SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' compile;' FROM USER_OBJECTS
WHERE
STATUS='INVALID' AND OBJECT_TYPE='PROCEDURE';