Monday, December 31, 2012

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>

No comments:

Post a Comment