When applying patches or making changes to a database I often invalidate many objects with one change. As long as the object requires no changes, the following SQL*Plus script will recompile any object that is invalid:
set feedback off
set verify off
set echo off
set pagesize 0
set heading off
spool compile.sql
select 'alter ' ||
decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
' ' ||
object_name||
' compile' ||
decode(object_type, 'PACKAGE BODY', ' body;', ';')
from dba_objects
where status = 'INVALID'
/
spool off
set feedback on
set verify on
set heading on
set pagesize 40
@compile
