记录一则ORACLE MOVE操作后重建索引过程被强制中断导致的ORA-8104案例

环境：SunOS + Oracle 11.2.0.3

对部分表进行Move操作之后，未重建对应的索引，会导致ORA-1502 索引不可用。

此时需要用下面的查询拼接出重建不可用索引的sql语句：

select 'alter index '||index_name||' rebuild tablespace dbs_cssf_gt online;' from user_indexes where status = 'UNUSABLE' order by index_name desc;

重建过程中个别索引重建的等待时间过长，直接ctrl+c终止了，然后查询哪些索引状态还是unusable.发现如下三个索引：

WONO_CSSF_GT_HUB_INPUT_PARAMS
PK_CSSF_GT_HUB_INPUT_PARAMS

PK_CSSF_CP_SH_INPUT_PARAMS

1.联机重建前两个索引时报错ORA-8104  该索引对象265028正在被联机建立或重建

alter index WONO_CSSF_GT_HUB_INPUT_PARAMS rebuild tablespace dbs_cssf_gt online;
alter index PK_CSSF_GT_HUB_INPUT_PARAMS rebuild tablespace dbs_cssf_gt online;

2.drop索引仍然报错ORA-8104

drop index WONO_CSSF_GT_HUB_INPUT_PARAMS; --报错ORA-8104
alter table CSSF_GT_HUB_INPUT_PARAMS drop primary key; --报错ORA-2441 提示主键不存在，发现主键已经删除，但对应的索引还在，删掉索引。
drop index PK_CSSF_GT_HUB_INPUT_PARAMS;--报错ORA-8104

3.查询此表记录数

select count(1) from CSSF_GT_HUB_INPUT_PARAMS;  --13条记录而已

4.重建主键和索引

alter table CSSF_GT_HUB_INPUT_PARAMS
  add constraint PK_CSSF_GT_HUB_INPUT_PARAMS primary key (WF_SN)
  using index
  tablespace DBS_CSSF_GT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create index WONO_CSSF_GT_HUB_INPUT_PARAMS on CSSF_GT_HUB_INPUT_PARAMS (WO_NO)
  tablespace DBS_CSSF_GT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

5.重建第三个无效索引时遭遇 ORA-604 ORA-1450,但之后drop index成功，然后试图drop上面两个index也成功了，估计是SMON已经回收了之前的失败过程。

SQL> alter index PK_CSSF_CP_SH_INPUT_PARAMS rebuild tablespace dbs_cssf_gt online;

alter index PK_CSSF_CP_SH_INPUT_PARAMS rebuild tablespace dbs_cssf_gt online

ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01450: 超出最大的关键字长度 (3215)

SQL> alter table  CSSF_CP_SH_INPUT_PARAMS drop primary key;

alter table  CSSF_CP_SH_INPUT_PARAMS drop primary key

ORA-02441: 无法删除不存在的主键

SQL>
SQL> alter table CSSF_CP_SH_INPUT_PARAMS
  2    add constraint PK_CSSF_CP_SH_INPUT_PARAMS primary key (WF_SN)
  3    using index
  4    tablespace DBS_CSSF_GT
  5    pctfree 10
  6    initrans 2
  7    maxtrans 167
  8    storage
  9    (
10      initial 64K
11      next 1M
12      minextents 1
13      maxextents unlimited
14    );

alter table CSSF_CP_SH_INPUT_PARAMS
  add constraint PK_CSSF_CP_SH_INPUT_PARAMS primary key (WF_SN)
  using index
  tablespace DBS_CSSF_GT
  pctfree 10
  initrans 2
  maxtrans 167
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )

ORA-00955: 名称已由现有对象使用

SQL>   drop index PK_CSSF_CP_SH_INPUT_PARAMS;

Index dropped

SQL>
SQL> alter table CSSF_CP_SH_INPUT_PARAMS
  2    add constraint PK_CSSF_CP_SH_INPUT_PARAMS primary key (WF_SN)
  3    using index
  4    tablespace DBS_CSSF_GT
  5    pctfree 10
  6    initrans 2
  7    maxtrans 167
  8    storage
  9    (
10      initial 64K
11      next 1M
12      minextents 1
13      maxextents unlimited
14    );

Table altered

附一篇网上讨论ora-8104的资料：http://blog.csdn.net/wyzxg/article/details/4318618

6.总结：

索引对象的创建操作要慎重，开始创建后不要轻易中断创建过程。