[How To] Find Un-Usable Partitions of the Invalid Index

To find out the invalid Index, please read my previuos post here. Once you get the name of the invalid Index, use that name in the below query to get the list of unusable Partitions on that Index.

SELECT INDEX_NAME,
PARTITION_NAME,
STATUS
FROM ALL_IND_PARTITIONS WHERE INDEX_NAME=
'NAME_OF_INDEX' ANDSTATUS='UNUSABLE';
Replace the 'NAME_OF_INDEX' with the name of Index in question.

Thanks for visiting !!