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 vote306 views1 answer
Sql server order by nulls last
SQL
4 votes329 views3 answers
Sql drop primary key
SQL
12 votes320 views2 answers
Delete all entries postgres
SQL
49 votes367 views6 answers
Get duplicate records in sql
SQL
1 vote303 views2 answers
How to check username in mysql command line
SQL
0 vote297 views1 answer
Ms Sql set us timezone
SQL
0 vote352 views1 answer
Having all mysql
SQL
0 vote321 views3 answers
Postgresql allow remote connections
SQL
0 vote306 views1 answer
Mysql set boolean default value
SQL
0 vote308 views1 answer
Create function syntax sql server
SQL