ORA-01502: index or partition of such index is in unusable state
August 26th, 2009 Posted in Oracle How-To
Do you ever get and ORA-01502? This error code says your index is in an unusable state. It’s easy to find these indexes.
select index_name, status, owner from dba_indexes where STATUS = 'UNUSABLE';
If you want to build the rebuild SQL that is easy too.
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where STATUS = 'UNUSABLE';
