rem======================================================================
rem script to compile all invalid objects in the database
rem
rem user should have dba privilege to execute this
rem OPDE
rem======================================================================
set termout off echo off pages 0 trims off lines 100
spool /tmp/cinv.sql
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
|| ' '||owner||'.'|| object_name ||' compile' ||
decode(object_type, 'PACKAGE BODY', ' body;', ';')
from dba_objects
where status = 'INVALID'
/
spool off
set termout on feedback on echo on
spool /tmp/cinvobj.lis
@/tmp/cinv.sql

column owner format A6
column object_name format A30
column object_type format A20
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
/
spool off
set lines 80 
prompt Spool file saved at /tmp/cinvobj.lis
prompt Script file saved at /tmp/cinv.sql
prompt