索引失效造成的ORA-01502

ORA-01502: 索引'TSTUSER.IDXT'或这类索引的分区处于不可用状态


这个错误是由于索引失效造成的，重建索引后，问题就解决了。


为了搞清楚索引为什么会失效，以及如何解决，做个测试：
MOVE 表后会引启索引失效，会出现此问题。




SQL> drop table t;


表已删除。


SQL> create table t(a number);


表已创建。


SQL> select tablespace_name from user_segments where segment_name='T';


TABLESPACE_NAME                                                                
------------------------------------------------------------                   
USED_TEST                                                                      
创建一个普通索引
SQL> create index idxt on t(a);


索引已创建。


SQL>  insert into t values(10);


已创建 1 行。


SQL> set linesize 200


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ----------------       


                                                                         
IDXT            NORMAL   USED_TEST                                                    TABLE           VALID                  


                                                                         
           
 模拟索引是失效的情况：                                                                                      


SQL> alter table  t move tablespace tools
  2  ;


表已更改。


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDXT            NORMAL   USED_TEST                                                    TABLE           UNUSABLE               


                                                                         


SQL> select * from t;


         A                                                                                                                   


                                                                         
----------                                                                                                                   


                                                                         
        10                                                                                                                   


                                                                         


SQL> insert into t values(11);
insert into t values(11)
*
第 1 行出现错误:
ORA-01502: 索引'TSTUSER.IDXT'或这类索引的分区处于不可用状态




我们看到，当使用类似 alter table xxxxxx move tablespace xxxxxxx 命令后，索引就会失效。
作为测试，也可以直接使用alter index idxt unusable;命令使索引失效


对于普通表中的不同索引（非唯一索引），我们有两种方法解决这个问题。
方法一：设置 skip_unusable_indexes=true;


SQL> alter session set skip_unusable_indexes=true;


会话已更改。


SQL> insert into t values(11);


已创建 1 行。


SQL> commit;


提交完成。




SQL> select * from t;


         A                                                                                                                   


                                                                         
----------                                                                                                                   


                                                                         
        10                                                                                                                   


                                                                         
        11                                                                                                                   


                                                                         


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDXT            NORMAL   USED_TEST                                                    TABLE           UNUSABLE               
现在我们看到，这个索引的状态虽然还是“UNUSABLE”但是，通过设置“alter session set skip_unusable_indexes=true;”，
我们已经可以访问这个表了，但是请注意，这种情况下，这个索引是不可用的，也就是说优化器在考虑是否要使用索引时是不考虑这个所以的


。


方法2：通过常见所以彻底解决这个问题
首先，先设置 “skip_unusable_indexes=false”，也就是不跳过失效索引                                                           


SQL> alter session set skip_unusable_indexes=false;


会话已更改。


SQL> insert into t values(12);
insert into t values(12)
*
第 1 行出现错误:
ORA-01502: 索引'TSTUSER.IDXT'或这类索引的分区处于不可用状态


然后重建这个失效的索引
SQL> alter index idxt rebuild;


索引已更改。


SQL> insert into t values(13);


已创建 1 行。


SQL> commit;


提交完成。


SQL> select * from t;


         A                                                                                                                   


                                                                         
----------                                                                                                                   


                                                                         
        10                                                                                                                   


                                                                         
        11                                                                                                                   


                                                                         
        13                                                                                                                   


                                                                         


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDXT            NORMAL   USED_TEST                                                    TABLE           VALID                  


                                                                         
重建索引才是解决这类问题的彻底的方法。


现在，我们建立一个唯一索引来看看：
SQL> drop table t;


表已删除。


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';


未选定行


SQL> create table t(a number);


表已创建。


SQL> create unique index idx_t on t(a);


索引已创建。


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T';


未选定行


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDX_T           NORMAL   USED_TEST                                                    TABLE           VALID                  


                                                                         


SQL> insert into t values(1);


已创建 1 行。


SQL> commit;


提交完成。


SQL> select * from t;


         A                                                                                                                   


                                                                         
----------                                                                                                                   


                                                                         
         1                                                                                                                   


                                                                         
将索引手工修改为unusable状态（模拟发生索引失效的情况）：
SQL> alter index idx_t unusable;


索引已更改。


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDX_T           NORMAL   USED_TEST                                                    TABLE           UNUSABLE               


                                                                         


SQL> insert into t values(2);
insert into t values(2)
*
第 1 行出现错误:
ORA-01502: 索引'TSTUSER.IDX_T'或这类索引的分区处于不可用状态


首先，我们通过重建索引（rebuild index）的方法来解决问题：
SQL> alter index idx_t rebuild;


索引已更改。


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDX_T           NORMAL   USED_TEST                                                    TABLE           VALID                  


                                                                         


SQL> insert into t values(3);


已创建 1 行。


SQL> commit;


提交完成。


SQL> select * from t;


         A                                                                                                                   


                                                                         
----------                                                                                                                   


                                                                         
         1                                                                                                                   


                                                                         
         3                                                                                                                   


                                                                         
现在我们再次模拟索引失效(unusable状态)：
SQL> alter index idx_t unusable;


索引已更改。


SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDX_T           NORMAL   USED_TEST                                                    TABLE           UNUSABLE               


                                                                         


SQL> insert into t values(4);
insert into t values(4)
*
第 1 行出现错误:
ORA-01502: 索引'TSTUSER.IDX_T'或这类索引的分区处于不可用状态


然后，看看是否可以通过设置参数skip_unusable_indexes=true来解决问题：
SQL> alter session set skip_unusable_indexes=true;


会话已更改。


SQL> insert into t values(4);
insert into t values(4)
*
第 1 行出现错误:
ORA-01502: 索引'TSTUSER.IDX_T'或这类索引的分区处于不可用状态




SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';


INDEX_NAME      INDEX_TY TABLESPACE_NAME                                              TABLE_TYPE      STATUS                 


                                                                         
--------------- -------- ------------------------------------------------------------ --------------- ---------------        


                                                                         
IDX_T           NORMAL   USED_TEST                                                    TABLE           UNUSABLE               


                                                                         


SQL> alter index idx_t rebuild;


索引已更改。


SQL> insert into t values(4);


已创建 1 行。


SQL> commit;


提交完成。


SQL> select * from t;


         A                                                                                                                   


                                                                         
----------                                                                                                                   


                                                                         
         1                                                                                                                   


                                                                         
         3                                                                                                                   


                                                                         
         4                                                                                                                                                                                            


SQL> insert into t values(4);
insert into t values(4)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TSTUSER.IDX_T)




SQL> spool off;


很显然，对于unique index，通过简单的设置参数是不能解决问题的，要解决unique index 失效的问题，只能通过重建索引来实现。