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 失效的问题,只能通过重建索引来实现。
这个错误是由于索引失效造成的,重建索引后,问题就解决了。
为了搞清楚索引为什么会失效,以及如何解决,做个测试:
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 失效的问题,只能通过重建索引来实现。