Pages

Monday, November 8, 2010

Check for view recompilation

Use the following to identify which DB's need view recompilation by using the following PL/SQL as the parameter for a SQL Script job:


WHENEVER SQLERROR EXIT FAILURE;
declare
xvar number;
begin
SELECT count(*) into xvar FROM sys.registry$history where ID = '6452863';
if xvar=0 then
dbms_output.put_line('Needs View Recompliation');
raise_application_error(-20101,'Needs View Recompilation');
else
dbms_output.put_line('View Recompliation Already Done');
end if;
end;
/


Run that against all targets, any that fail likely need the view recompilation job run.