Report all invalid objects in a database

Report all invalid objects in a database

This report will show you all objects in the database that have an invalid status. This may be a normal condition in many cases, or it may indicate that an object has been removed. The removal of that object will cause the dependent objects to go invalid.

REM LOCATION:   Object Management\Database Reports

REM FUNCTION:   Show all invalid objects in database

REM TESTED ON:  7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6

REM PLATFORM:   non-specific

REM REQUIRES:   dba_objects

REM.

REM

REM ************** Knowledge Xpert for Oracle Administration ******************

COLUMN object_name      FORMAT A30      HEADING 'Object|Name'

COLUMN owner            FORMAT a10      HEADING 'Object|Owner'

COLUMN last_time        FORMAT a20      HEADING 'Last Change|Date'

SET LINES 80 FEEDBACK OFF PAGES 0 VERIFY OFF

START title80 'Invalid Database Objects'

SPOOL rep_out/inv_obj

SELECT owner, object_name, object_type,

       TO_CHAR (last_ddl_time, 'DD-MON-YY hh:mi:ss') last_time

  FROM dba_objects

 WHERE status = 'INVALID'

/

SET LINES 80 FEEDBACK ON PAGES 22 VERIFY ON

CLEAR COLUMNS

TTITLE OFF