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

5 votes188 views3 answers
Postgres show databases
SQL
0 vote169 views1 answer
10000000000000000000 am to meters
SQL
1 vote212 views1 answer
Sql random sampling per group
SQL
0 vote175 views1 answer
Create new table pl sql
SQL
1 vote192 views1 answer
Sql not equal multiple columns
SQL
5 votes184 views2 answers
Sql constraint check value in list
SQL
0 vote194 views4 answers
Mysql desactivar trigger
SQL
0 vote193 views1 answer
Get statis values sql
SQL
0 vote172 views1 answer
How to access to mysql without root
SQL
3 votes195 views2 answers
Oracle drop column
SQL