oracle find unusable indexes
SELECT OWNER, INDEX_NAME, NULL AS PARTITION_NAME, NULL AS SUBPARTITION_NAME, TABLESPACE_NAME FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' UNION ALL -- Index partitions: SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, NULL AS SUBPARTITION_NAME, TABLESPACE_NAME FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE' UNION ALL -- Index subpartitions: SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME FROM DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE';
