Script to list missing and INVALID Objects in the database

Script to list missing and INVALID Objects in the database

REM Script to list missing and INVALID Objects in the database
REM
REM MISSING.SQL
REM
REM This script recompiles all objects that have become invalidated
REM
REM It should be run as SYS or SYSTEM
REM

set pagesize 0
set linesize 120
set feedback off
set trimspool on
set termout on

spool missing.txt

select A.Owner Oown,
A.Object_Name Oname,
A.Object_Type Otype,
'Miss Pkg Body' Prob
from DBA_OBJECTS A
where A.Object_Type = 'PACKAGE'
and A.Owner not in ('SYS','SYSTEM')
and not exists
(select 'x'
from DBA_OBJECTS B
where B.Object_Name = A.Object_Name
and B.Owner = A.Owner
and B.Object_Type = 'PACKAGE BODY')
union
select Owner Oown,
Object_Name Oname,
Object_Type Otype,
'Invalid Obj' Prob
from DBA_OBJECTS
where Owner not in ('SYS','SYSTEM')
and Status != 'VALID'
order by 1,4,3,2
/
spool off