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 vote267 views1 answer
Call function sql oracle with output put line
SQL
2 votes154 views1 answer
Sql select min value from multiple columns
SQL
3 votes191 views2 answers
Postgresql remove not null constraint
SQL
0 vote194 views1 answer
Mysql 5.7 in centos 7
SQL
0 vote246 views2 answers
The metadata storage is not up to date, please run the sync-metadata-storage command to fix this issue.
SQL
0 vote196 views1 answer
Normalization in database 2nd normal form
SQL
16 votes204 views4 answers
Mysql import from sql file
SQL
2 votes229 views2 answers
Sql query to list all tables in a database sql server
SQL
2 votes198 views2 answers
Postgres select duplicate columns
SQL
5 votes197 views2 answers
Oracle timestamp to date
SQL