索引失效造成的ORA-01502
上一篇 / 下一篇 2008-05-06 16:26:27 / 个人分类：索引
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 失效的问题，只能通过重建索引来实现。
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 1 | 2 | 3 | |||||||
| 4 | 5 | 6 | 7 | 8 | 9 | 10 | |||
| 11 | 12 | 13 | 14 | 15 | 16 | 17 | |||
| 18 | 19 | 20 | 21 | 22 | 23 | 24 | |||
| 25 | 26 | 27 | 28 | 29 | 30 | 31 | |||
数据统计
- 访问量: 158
- 日志数: 29
- 书签数: 2
- 建立时间: 2008-04-29
- 更新时间: 2008-05-16
