isitdevops.com/databases

— cre_re_bld_objs.sql
— adb 18/11/02
— create a script to re-build all invalid objects, inc sys ones.
—
set pagesize 2000
set linesize 100
set head off
set feed off
set verify off
set pause off
set escape \

spool re_bld_objs.sql

select ‘alter procedure ‘|| owner||’.’||object_name
      ||’ compile;’
from dba_objects
where status != ‘VALID’
and   object_type = ‘PROCEDURE’;

select ‘alter function ‘|| owner||’.’||object_name
      ||’ compile;’
from dba_objects
where status != ‘VALID’
and   object_type = ‘FUNCTION’;

select ‘alter package ‘|| owner||’.’||object_name
      ||’ compile;’
from dba_objects
where status != ‘VALID’
and   object_type = ‘PACKAGE’;

select ‘alter package ‘|| owner||’.’||object_name
      ||’ compile body;’
from dba_objects
where status != ‘VALID’
and   object_type = ‘PACKAGE BODY’;

select ‘alter view ‘|| owner||’.’|| object_name
      ||’ compile;’
from dba_objects
where status != ‘VALID’
and   object_type = ‘VIEW’;

spool off

set head on
select count(*) “Invalid count:” from dba_objects where status != ‘VALID’;

prompt
pause Hit RETURN to compile or ctrl-d to quit…

spool comp.lst

@re_bld_objs

select count(*) “Invalid count:” from dba_objects where status != ‘VALID’;

prompt
prompt (several runs may be reqd if you have complex dependencies)

spool off