如何清除建立失敗的索引

在建立一張大表的索引時，因為沒有加並行而導致建立時間很長，這時候如果終止操作，比如直接關閉終端，然後再次登入建立時就會報錯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、資料庫備份恢復及遷移經驗。