ORA-01502

This example was tested on Oracle 11.2.0.2.7. It looks at why an index might become unusable. First I created a table in the SYSTEM tablespace by mistake:

SQL> create table andrews_table

  2  tablespace system

  3  as select * from dba_tables

  4  /

 

Table created.

 

SQL>

I created an index on the table and checked that it was VALID:

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Index created.

 

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

Then I made sure that Oracle would tell me if it tried to use an UNUSABLE index:

SQL> alter session

  2  set skip_unusable_indexes = false

  3  /

 

Session altered.

 

SQL>

I ran some SQL which would use the index and it worked OK:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I moved the table to the correct tablespace:

SQL> alter table andrews_table

  2  move tablespace users

  3  /

 

Table altered.

 

SQL>

This invalidated the index:

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

UNUSABLE

 

SQL>

So when I tried to use it I got an ORA-01502:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

select count(*) from andrews_table

*

ERROR at line 1:

ORA-01502: index 'OPS$ORACLE.ANDREWS_INDEX' or

partition of such index is in unusable state

 

SQL>

To fix this, I rebuilt the index:

SQL> alter index andrews_index rebuild

  2  /

 

Index altered.

 

SQL>

This made the index VALID:

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

... and I was able to use it again:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>