索引rebuild online失敗後處理

BTxigua發表於2011-12-20
索引Rebuild
索引rebuild online失敗，報ORA-08104錯誤，也無法drop索引重建：
SQL> alter index UCR_CRM2.IDX_TF_R_TICKET_IDLE_LEVEL rebuild online ;
alter index UCR_CRM2.IDX_TF_R_TICKET_IDLE_LEVEL rebuild online
ORA-08104: this index object 692608 is being online built or rebuilt
SQL> drop index UCR_CRM2.IDX_TF_R_TICKET_IDLE_LEVEL ;
drop index UCR_CRM2.IDX_TF_R_TICKET_IDLE_LEVEL
ORA-08104: this index object 692608 is being online built or rebuilt

找到一個對應10g的方法，可以用來處理這種情況，但結果失敗了，報resource busy
SQL> desc dbms_repair.online_index_clean
Parameter     Type           Mode Default?
------------- -------------- ---- --------
(RESULT)      BOOLEAN                     
OBJECT_ID     BINARY_INTEGER IN   Y       
WAIT_FOR_LOCK BINARY_INTEGER IN   Y     
SQL> DECLARE
  2    RetVal BOOLEAN;
  3    OBJECT_ID BINARY_INTEGER;
  4    WAIT_FOR_LOCK BINARY_INTEGER;
  5 
  6  BEGIN
  7    OBJECT_ID := 692452;
  8  -- 
  9    WAIT_FOR_LOCK := NULL;
 10    RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
 11    COMMIT;
 12  END;
 13  /
DECLARE
  RetVal BOOLEAN;
  OBJECT_ID BINARY_INTEGER;
  WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
  OBJECT_ID := 692452;
  WAIT_FOR_LOCK := NULL;
  RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
  COMMIT;
END;
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SYS.DBMS_REPAIR", line 475
ORA-06512: at line 10

意外滴，rename一把表，竟然可以成功重建索引了：
SQL> alter table ucr_crm2.TF_R_TICKET_IDLE rename to TF_R_TICKET_IDLE_old ;
Table altered
Executed in 0.11 seconds
SQL> alter table ucr_crm2.TF_R_TICKET_IDLE_old rename to TF_R_TICKET_IDLE ;
Table altered
Executed in 0.016 seconds
SQL> alter index ucr_crm2.IDX_TF_R_TICKET_IDLE_LEVEL rebuild online ;
Index altered

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10867315/viewspace-713605/，如需轉載，請註明出處，否則將追究法律責任。

相關文章