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 vote152 views1 answer
Grant owner postgresql
SQL
0 vote144 views1 answer
Postgres declare variable
SQL
20 votes183 views4 answers
How to find average in sql
SQL
0 vote156 views1 answer
Sql select merge multiple values
SQL
5 votes145 views1 answer
Find table from column name in sql
SQL
0 vote163 views1 answer
Declaring variables in pl sql
SQL
0 vote157 views2 answers
Athena drop partition
SQL
3 votes153 views1 answer
Oracle get trigger ddl
SQL
3 votes223 views1 answer
Sql add two values together
SQL
1 vote160 views1 answer
Increase date by one day in sql oracle
SQL