ORA-01502 state unusable错误成因和解决方法(二)

原创 2005年03月01日 16:11:00

SQL> create table t(a number);

Table created.

现在,我们建立一个唯一索引来看看:
SQL> create unique index idx_t on t(a);

Index created.

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

no rows selected

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

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T                          NORMAL                      DATA_DYNAMIC                   TABLE       VALID

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

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

Index altered.

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

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T                          NORMAL                      DATA_DYNAMIC                   TABLE       UNUSABLE

我们看到这是,已经不能正常往表中插入数据:
SQL> insert into t values(2);
insert into t values(2)
*
ERROR at line 1:
ORA-01502: index 'MISC.IDX_T' or partition of such index is in unusable state

首先,我们通过重建索引(rebuild index)的方法来解决问题:
SQL> alter index idx_t rebuild;

Index altered.

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

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T                          NORMAL                      DATA_DYNAMIC                   TABLE       VALID

SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL>

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

Index altered.

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

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T                          NORMAL                      DATA_DYNAMIC                   TABLE       UNUSABLE

SQL> insert into t values(3);
insert into t values(3)
*
ERROR at line 1:
ORA-01502: index 'MISC.IDX_T' or partition of such index is in unusable state

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

Session altered.

SQL> insert into t values(3);
insert into t values(3)
*
ERROR at line 1:
ORA-01502: index 'MISC.IDX_T' or partition of such index is in unusable state

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

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T                          NORMAL                      DATA_DYNAMIC                   TABLE       UNUSABLE

SQL> alter index idx_t rebuild;

Index altered.

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

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                TABLE_TYPE  STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T                          NORMAL                      DATA_DYNAMIC                   TABLE       VALID

SQL> insert into t values(3);

1 row created.

SQL> commit;

Commit complete.

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

 

周哥跟你谈谈网页布局容易犯的错误

-
  • 1970年01月01日 08:00

索引错误 index VHL_V6.PK_WEB_APP_TGT_OBJ or partition of such index is in unusable state 的解决办法

今天在选择一条记录进行做删除操作时,碰见index失败的问题,如下:处理失败!错误信息:[SQLException  ORA-01502  index  VHL_V6.PK_WEB_APP_TGT_O...
  • cnham
  • cnham
  • 2009-09-28 10:18:00
  • 9042

ORA-20000: index "FCSASUSER"."IDX_DN_TREE_ID" or partition of such index is in unusable state

alert日志发现如下报错: Sat Jul 30 06:00:12 CST 2016 Errors in file /u01/app/oracle/admin/crfdw/bdump/crfdw_j...
  • EVISWANG
  • EVISWANG
  • 2016-08-02 16:50:38
  • 705

ORA-01502 state unusable错误成因和解决方法(一)

接到开发人员和业务人员的通知,说一个登陆页面不能用了,报错:2005-01-31 13:59:02,721 [com.aspire.common.dao.OamUserDAO]- -214:selec...
  • lunar2000
  • lunar2000
  • 2005-02-03 18:04:00
  • 2016

OGG-00665 (status = 1502-ORA-01502: index 'index' or partition of such index is in unusable state)

今天同事在新搭建的压力测试环境启动某个 OGG replicat 进程时报了如下错误: 2013-05-14 16:37:41  ERROR   OGG-00665  OCI Error e...
  • xiangsir
  • xiangsir
  • 2013-05-14 23:12:34
  • 2780

分析和解决 ORA-01502: index '某分区表索引名' or partition of such index is in unusable state

最近工作中遇到一个Oracle的数据库问题 逻辑背景:      由于Performance 原因重构一些数据库表(其中包含了一些分区数据表) 同级联删除旧的index 和 新建了inde...
  • w304807481
  • w304807481
  • 2013-01-14 14:33:45
  • 895

解决ORA-01502 state unusable错误成因

接到开发人员和业务人员的通知,说一个登陆页面不能用了,报错:      2005-01-31 13:59:02,721 [com.aspire.common.dao.OamUserDAO]- -2...
  • adermxl
  • adermxl
  • 2014-06-18 18:53:21
  • 288

ORA-01502错误成因和解决方法

ORA-01502: index 'XXXXXXXX' or partition of such index is in unusable
  • tfxcg1026
  • tfxcg1026
  • 2015-12-16 09:09:52
  • 246

oracle 脚本(2)-修复 status 为 unusable 的 index(ORA-01502)

有配置工具使用时, 经常会出现以下错误: ORA-01502: index 'xxx.xxxxx' or partition of such index is in unusable state,...
  • zeng_lingfan
  • zeng_lingfan
  • 2011-09-03 00:41:21
  • 1864

(转)ORA-01502 state unusable錯誤成因和解決方法

接到開發人員和業務人員的通知,說一個登陸頁面不能用了,報錯: 2005-01-31 13:59:02,721 [com.aspire.common.dao.OamUserDAO]- -214:sel...
  • jackyrongvip
  • jackyrongvip
  • 2013-07-01 16:21:06
  • 384
收藏助手
不良信息举报
您举报文章:ORA-01502 state unusable错误成因和解决方法(二)
举报原因:
原因补充:

(最多只允许输入30个字)