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

1 vote402 views1 answer
Tsql replace value in json
SQL
1 vote382 views1 answer
Oracle nvl2
SQL
20 votes370 views6 answers
Creating a table in sql
SQL
0 vote354 views1 answer
Mssql check if date is greater than today
SQL
0 vote391 views1 answer
Postgresql like
SQL
15 votes392 views4 answers
Sql update from select
SQL
3 votes396 views2 answers
How to force truncate a table in mysql
SQL
1 vote363 views2 answers
Mysql remove first and last character from string
SQL
0 vote368 views1 answer
How to order result of subquery in select
SQL
0 vote376 views1 answer
How to find table lock and row lock in mysql
SQL