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 vote130 views1 answer
ERROR: column "hourly_visitors.hour" must appear in the GROUP BY clause or be used in an aggregate function
SQL
0 vote115 views2 answers
Mysql updating multiple column values from array variable
SQL
0 vote117 views1 answer
Sql count * vs count 1
SQL
0 vote72 views2 answers
Denormalization in sql
SQL
4 votes224 views6 answers
Laravel jwt
SQL
5 votes173 views1 answer
Oracle source query
SQL
0 vote171 views1 answer
Drop view sql
SQL
0 vote124 views1 answer
Create table split string function in sql server
SQL
0 vote119 views1 answer
Initcap in mysql
SQL
0 vote158 views1 answer
Mysql failed to login as root@localhost
SQL