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 vote392 views1 answer
Mysql error 1114 (hy000) the table is full
SQL
0 vote383 views1 answer
Updating database in sql
SQL
61 votes563 views6 answers
Mysql add column
SQL
0 vote561 views1 answer
Multiple row join
SQL
1 vote383 views3 answers
How to pass password to mysql command line
SQL
0 vote378 views1 answer
Wordpress delete post revisions older than date "sql"
SQL
0 vote376 views1 answer
Stored procedures example
SQL
10 votes355 views5 answers
Between keyword in sql
SQL
1 vote337 views1 answer
Oracle create program if no exists
SQL
1 vote373 views2 answers
Select into temp table
SQL