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

92 votes463 views6 answers
Substring
SQL
0 vote290 views1 answer
MySQL - Enabling the Event Scheduler
SQL
1 vote264 views1 answer
Access denied for user 'root'@'localhost' python sql-connect error
SQL
3 votes259 views2 answers
Oracle apex charging debug
SQL
25 votes331 views5 answers
Mysql replace text
SQL
0 vote250 views1 answer
Mysql regex exact match
SQL
1 vote258 views1 answer
Postgres convert exisiting column to text
SQL
1 vote206 views1 answer
Postgres reset auto increment
SQL
10 votes261 views1 answer
Oracle object dependencies
SQL
11 votes278 views6 answers
Distinct sql
SQL