Query To Get Count and List of Invalid Objects

Download(invalids.sql)

REM +======================================================================+
REM                    
REM File Name: invalids.sql
REM 
REM Description:
REM   Query To Get Count and List of Invalid Objects
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @invalids.sql
REM   
REM +======================================================================+

PROMPT
PROMPT >>>Invalid Objects Count<<<
PROMPT

clear columns
set lines 180
compute sum label "Total Invalid Count" of count on report
break on report
select owner, count(*) count from all_objects where status = 'INVALID' group by owner;

PROMPT
PROMPT >>>List of Invalid Objects<<<
PROMPT

clear columns
SET lines 180
SET pages 150
COL OWNER for a12
SELECT   owner, 
         object_name, 
         object_type, 
         TO_CHAR (created, 'DD-MON-YYYY HH24:MI:SS') "CREATED", 
         TO_CHAR (last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') "LAST_DDL_TIME", 
         TIMESTAMP, 
         status
    FROM all_objects
   WHERE status = 'INVALID'
ORDER BY TO_DATE (last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') ASC;