索引rebuild online失败后处理

索引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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10867315/viewspace-713605/