Oracle create index 中途取消后应该如何处理

        有时会遇到这样的情况，在创建索引的过程中啪叽网断了，会话中断；或者创建索引时觉得执行太慢，直接按了crtl+c取消，直接把运行窗口关了...尝试重新建索引的时候会遇到报错ORA-00095 索引名已存在，而drop index时又报错ORA-08104 this index is being online built or rebuilt

查看ORA-08104介绍

oerr ora 08104
08104, 00000, "this index object %s is being online built or rebuilt"
// *Cause:  the index is being created or rebuild or waited for recovering 
//          from the online (re)build 
// *Action: wait the online index build or recovery to complete

一、 报错原理

在进行online rebuild | create 时，Oracle 会修改如下信息：

• 修改ind$中索引的flags，将该flags+512. 关于flags的含义，在下面进行说明。
• 在该用户下创建一个临时日志表 （表名为sys_journal_<object_id>）来保存在创建或者重建索引期间产生的日志信息。

如果操作异常结束，而Oracle的SMON进程还没来得及清理journal table和ind$的flags标志位，系统会认为online (re)build操作还在执行，因此在drop索引时会报错 this index object %s is being online built or rebuilt

关于ind$中的flags字段

Flag字段的说明可以在ind$的sql.bsq脚本中找到：

/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */

异常终止的情况下，可以发现ind$关于该索引的状态还是online rebuild的：

SQL> select obj#,flags from ind$ where obj#=67420;

OBJ# FLAGS
——— ———
67420 514

-- 514=0x202，表示该索引状态为index is being online rebuilt: 0x200 + analyzed: 0x02

在SMON完成清理动作后，再次查询索引状态已经恢复正常：

SQL> select obj#,flags from ind$ where obj#=67420;

OBJ# FLAGS
———- ———-
67420 2

二、 解决方法

1. 等待SMON进程清理

根据上面的原理，如果不着急的话，可以等待SMON进程自己去清理

2. 手动清理

首先查询问题索引的object_id

select object_id from dba_objects where object_name='YOUR_INDEX_NAME';

然后执行以下语句

declare
  isClean boolean;

begin
  isClean := FALSE;
  while isClean=FALSE loop
    isClean := dbms_repair.online_index_clean(查到的object_id,
                                              dbms_repair.lock_wait);
    dbms_lock.sleep(2);
  end loop;

  exception 
    when others then 
      RAISE; 
end;
/

• dbms_repair.lock_wait表示不断寻找资源锁，直到抢到为止（如果一直占不到锁，可能会运行非常长时间）
• 也可以指定 dbms_repair.all_index_id 清理所有问题索引

isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,
                                              dbms_repair.lock_wait);

取消一时爽，处理火葬场。对于大索引的online rebuild，不要轻易中止，否则可能要等上相当一段时间SMON才能完成清理工作，清理完后，可以在alert.log中看到如下记录：

User:,time:20071209 03:12:09,program:oracle@db1 (SMON),IP:,object:SYS_JOURNAL_67420,DDL: drop table “TAOBAO”.”SYS_JOURNAL_67420″

参考

ORA-600 [12813] When Dropping A Table Partition After a Failed IndexRebuild (文档 ID 803008.1)

http://www.ningoo.net/html/2007/dba_memo_online_rebuild_index_encounter_ora-08104.html

如何清除创建失败的索引_ITPUB博客