REM: Script to compile invalid objects in DB after refreshing
REM:
REM:*****************************************
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set pagesize 0
set feedback off
set trimspool on
prompt
prompt Run the script as sysdba otherwise invalid objects will remain (ORA-01031 error)
prompt
prompt Run the script several times. It only takes long the first time
prompt
prompt check the progress of compilation by issueing from another session
prompt select count(*) "invalid" from dba_objects where status <> \'VALID\';
prompt
prompt hit  to continue
pause
spool compile.lis
select \'alter \'||object_type||\' \'||owner||\'.\"\'||object_name||\'\" compile;\'
from dba_objects
where status<>\'VALID\'
and object_type not in (\'PACKAGE BODY\',\'TYPE BODY\',\'UNDEFINED\',\'JAVA CLASS\',\'SYNONYM\')
union
select \'alter package \'||owner||\'.\'||object_name||\' compile body;\'
from dba_objects
where status<>\'VALID\'
and object_type=\'PACKAGE BODY\'
union
select \'alter type \'||owner||\'.\'||object_name||\' compile body;\'
from dba_objects
where status<>\'VALID\'
and object_type=\'TYPE BODY\'
union
select \'alter materialized view \'||owner||\'.\'||object_name||\' compile;\'
from dba_objects
where status<>\'VALID\'
and object_type=\'UNDEFINED\'
union
select \'alter java class \'||owner||\'.\"\'||object_name||\'\" resolve;\'
from dba_objects
where status<>\'VALID\'
and object_type=\'JAVA CLASS\'
union
select \'alter synonym \'||owner||\'.\'||object_name||\' compile;\'
from dba_objects
where status<>\'VALID\'
and object_type=\'SYNONYM\'
and owner<>\'PUBLIC\'
union
select \'alter public synonym \'||object_name||\' compile;\'
from dba_objects
where status<>\'VALID\'
and object_type=\'SYNONYM\'
and owner=\'PUBLIC\';
spool off
set feedback on