SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

SET TERMOUT ON;

PROMPT

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Invalid Objects Summary |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

SET TERMOUT ON

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN owner FORMAT a25 HEADING 'Owner'

COLUMN object_name FORMAT a30 HEADING 'Object Name'

COLUMN object_type FORMAT a20 HEADING 'Object Type'

COLUMN count FORMAT 999,999,999 HEADING 'Count'

BREAK ON owner SKIP 2 ON REPORT

COMPUTE sum LABEL "Count: " OF count ON owner

COMPUTE sum LABEL "Grand Total: " OF count ON report

owner

, object_type

, count(*) Count

FROM dba_objects

WHERE status <> 'VALID'

GROUP BY owner, object_type