Monday, August 18, 2008

Unusable indexes can't be skipped if they are a primary key.

create table test(id integer primary key, text varchar2(10));
create index text_index on test(text);
insert into test values (1,'test1');
alter table test move tablespace users;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE

SQL> select table_name, index_name, status from user_indexes;

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
TEST SYS_C005223 UNUSABLE
TEST TEXT_INDEX UNUSABLE

SQL> select * from test where id = 1;

ID TEXT
---------- ----------
1 test1

SQL> update test set text = 'test2';

1 row updated.

SQL> update test set id = '2';
update test set id = '2'
*
ERROR at line 1:
ORA-01502: index 'ROBERT.SYS_C005223' or partition of such index is in unusable
state