Script to see invalid object (and unusable indexes)

set linesize   145
set pagesize  1000
set trimout     on
set trimspool   on
Set Feedback   off
set timing     off
set verify     off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   List of the invalid objects in Database                               ---

prompt -- ----------------------------------------------------------------------- ---

prompt

clear breaks
break on owner on object_type -
skip 1

column  owner         format a20   heading  "Owner"
column  object_name   format a35   heading  "Object Name"
column  object_type   format a20   heading  "Object Type"
column  status        format a10   heading  "Status"

Select
       owner
     , object_name
     , object_type
     , status
  from
       dba_objects
where
       status != 'VALID'
order
    by owner
     , object_type
     , object_name
;


prompt

prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   List of the Unusable Indexes in Database                              ---

prompt -- ----------------------------------------------------------------------- ---

prompt


clear breaks

column  owner            heading  'Owner'            format a10
column  index_owner      heading  'Index|Owner'      format a10
column  index_name       heading  'Index Name'       format a30
column  partition_name   heading  'Part|Name'        format a20
column  SUBPARTITION_name       heading  'Sub. Part|Name'   format a20
column  index_type       heading  'Index Type'       format a15
column  Table_Owner      heading  'Table|Owner'      format a10
column  Table_Name       heading  'Table|Name'       format a30
column  Table_type       heading  'Table|Type'       format a30



Select
       Owner
     , Index_Name
     , Index_Type
     , Table_Owner
     , Table_Name
     , Table_Type
From
       Dba_Indexes
Where
       status = 'UNUSABLE'
Order
    By Owner
     , Index_Name
     , Index_Type
;



prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   List of the Unusable Indexes Partitions in Database                   ---

prompt -- ----------------------------------------------------------------------- ---

prompt


Select
       index_Owner
     , Index_Name
     , partition_name
From
       DBA_IND_PARTITIONS
Where
       status = 'UNUSABLE'
Order
    By index_Owner
     , Index_Name
     , partition_name
;




prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   List of the Unusable Indexes SubPartitions in Database                ---

prompt -- ----------------------------------------------------------------------- ---

prompt

Select
       Index_Owner
     , Index_Name
     , partition_name
     , SUBPARTITION_NAME
From
       DBA_IND_SUBPARTITIONS
Where
       status = 'UNUSABLE' 
Order
    By Index_Owner
     , Index_Name
     , partition_name
     , SUBPARTITION_NAME
;





Prompt