[How To] List Invalid Index in Oracle

Finding an invalid Index is quite easy, however its a nightmare to find out only the non-system invalid Indexes. The below query can be used to find out all the invalid Indexes in Oracle 10g. The query is self-explanatory to anyone with a little knowledge of Oracle, hence I skip the explanation.

SELECT INDEX_NAME
FROM ALL_INDEXES
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
AND STATUS != 'VALID'AND(
STATUS !=
'N/A'OR INDEX_NAME IN(SELECT INDEX_NAME
FROM ALL_IND_PARTITIONS
WHERE STATUS != 'USABLE'AND(
STATUS !=
'N/A'OR INDEX_NAME IN(SELECT INDEX_NAME
FROM ALL_IND_SUBPARTITIONS
WHERE STATUS != 'USABLE')
)
)
);
 
Click here to read my blog post on how to find all the non-usable Partitions on an Invalid Index.

Hope this helps, thanks for visiting !