进行oracle开发时常常为不时失效的存储过程,函数等苦恼,于是下决心解决这个问题,东查西找,找到一个好东东,在基础上又修改了一下,问题终于被我解决了。
1。建立一个存储过程
CREATE OR REPLACE PROCEDURE TIMER_AUTO_RECOMPILE_OBJS AS
CURSOR OBJECTS_LIST IS
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE STATUS = 'INVALID';
BEGIN
FOR V_OBJECT IN OBJECTS_LIST LOOP
IF V_OBJECT.OBJECT_TYPE = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'alter procedure ' || V_OBJECT.OBJECT_NAME ||
' compile';
ELSEIF V_OBJECT.OBJECT_TYPE = 'FUNCTION' THEN EXECUTE IMMEDIATE 'alter function ' || V_OBJECT.OBJECT_NAME || ' compile';
ELSIF V_OBJECT.OBJECT_TYPE = 'VIEW' THEN
EXECUTE IMMEDIATE 'alter view ' || V_OBJECT.OBJECT_NAME || ' compile';
ELSIF V_OBJECT.OBJECT_TYPE = 'MATERIALIZED VIEW' THEN
EXECUTE IMMEDIATE 'alter materialized view ' || V_OBJECT.OBJECT_NAME ||
' compile';
END IF;
END LOOP;
END;
2。在需要的时候执行,或者建立一个定时任务
exec dbms_job.submit(:job_id,'timer_auto_recompile_objs;',sysdate,'sysdate+1/24');
定时执行