oracle check unusable indexes code example

Example: oracle find unusable indexes

-- Indexes:
SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';
-- Index partitions:
SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME
FROM DBA_IND_PARTITIONS
WHERE STATUS = 'UNUSABLE';
-- Index subpartitions:
SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS = 'UNUSABLE';