如何清除建立失敗的索引

在建立一張大表的索引時，因為沒有加并行而導緻建立時間很長，這時候如果終止操作，比如直接關閉終端，然後再次登入建立時就會報錯ORA-00095提示對象名字已被用，而去删除對象時又報錯ORA-08104索引正在被建立。

SQL> drop index xxx.BM_IX;
drop index xxx.BM_IX
                *
ERROR at line 1:
ORA-08104: this index object 1443829 is being online built or rebuilt

SQL> select object_id from dba_objects where object_name='BM_IX';

 OBJECT_ID
----------
   1443829

$ 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
           

從oerr工具看到的ORA-08104的解釋是索引并沒有建立失敗，而是在終端關閉之後，建立的操作還在繼續進行。建立或者重建索引時，系統會建立一個臨時日志表，這張表被用于存放建立或者重建索引期間産生的日志資訊，同時在基表IND$中這個索引的FLAG字段上會被設定為BUILD或者REBUILD辨別，當索引資訊變更時會把變更資訊存入日志表。如果索引建立或者重建失敗，這個日志表和資料字典中的狀态位都需要背景程序smon進行清理。

是以這裡的索引不能被删除是因為背景程序smon還沒來得及清理相應的臨時段和标志位，認為online rebuild操作還在進行。

那麼現在如何終止rebuild index這一操作？查找metalink得到一篇文檔：ORA-600 [12813] When Dropping A Table Partition After a Failed IndexRebuild (文檔 ID 803008.1)。它給出了兩種方法：

1）使用包dbms_repair包來清理

如果在出現問題的對象的資料庫活動能停下來，則直接簡單地執行如下語句即可：

connect / as sysdba
select dbms_repair.online_index_clean(<problem index object_id>) from dual; 
exit
           

2）使用PL/SQL block調用dbms_repair包來清理

如果在出現問題的對象的資料庫活動不能停下來，則如下的PL/SQL block來處理

注：加上dbms_repair.lock_wait表示不是立刻清理，需要不斷的尋找資源鎖，直到搶到為止

declare
  isClean boolean;
begin
  isClean := FALSE;
  while isClean=FALSE loop
isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,
                                          dbms_repair.lock_wait);
dbms_lock.sleep(2);
  end loop;
  exception 
when others then 
  RAISE; 
end;
/
           

| 作者簡介

管海濤·沃趣科技進階資料庫工程師

熟悉Oracle資料庫内部機制，豐富的資料庫及RAC叢集層故障診斷、性能調優、OWI、資料庫備份恢複及遷移經驗。