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 vote313 views2 answers
Athena drop partition
SQL
0 vote315 views1 answer
Search starting with mysql
SQL
0 vote295 views1 answer
Get first monday of month sql
SQL
0 vote321 views1 answer
Authentication modes in sql server
SQL
31 votes322 views4 answers
Mysql grant execute
SQL
-2 votes331 views2 answers
Add auto_increment column to existing table mysql
SQL
16 votes325 views1 answer
Mysql reset auto increment value
SQL
6 votes332 views4 answers
Mysql update set
SQL
4 votes303 views2 answers
Sql table
SQL
0 vote311 views2 answers
Jsonb
SQL