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';

Source: orafaq.com

Similar codes

0 vote212 views1 answer
USING THE NOT CONDITION IN SQL
SQL
1 vote219 views1 answer
Postgres left join
SQL
3 votes194 views2 answers
Sql asc
SQL
123 votes1.2K views5 answers
Sql where contains
SQL
0 vote200 views1 answer
Flexible search query delete table
SQL
10 votes209 views2 answers
Mysql remove only_full_group_by permanently
SQL
0 vote194 views1 answer
Pl/ sql change currency
SQL
4 votes196 views4 answers
Rollback in sql
SQL
0 vote235 views1 answer
How to take sum of column with same id and different table in SQL?
SQL
1 vote198 views1 answer
Postgresql how to show table names
SQL