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