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
No comments:
Post a Comment