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 vote171 views1 answer
Disable database droping sql
SQL
1 vote181 views1 answer
Mysql show long running queries
SQL
3 votes154 views2 answers
Oracle sql drop sequence
SQL
0 vote164 views1 answer
Import sql in postgresql
SQL
-1 vote192 views3 answers
SQL MM/dd/yyyy format
SQL
13 votes170 views4 answers
Command line mysql import
SQL
1 vote149 views1 answer
Declare table variable
SQL
9 votes167 views6 answers
Sql add column with default value
SQL
10 votes175 views6 answers
Sql between
SQL
-1 vote167 views1 answer
Count occurrences sql
SQL