dba_objects_invalid_count.sql

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Find all invalid objects counts in the database group by schemas,objects_types and status.
-- Call Syntax  : @dba_objects_invalid_count.sql
-- Last Modified: 20/03/2015
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET LINES 220 PAGES 49999
COLUMN owner FORMAT a30
COLUMN object_name FORMAT a30
SELECT owner,
       object_type,
       status,
       COUNT(*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY
  owner,
  object_type,
  status
ORDER BY
  owner,
  object_type,
  status;