ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引rebuild online失败后处理

索引rebuild online失败后处理

原创 Linux操作系统 作者:BTxigua 时间:2011-12-20 12:07:31 0 删除 编辑
索引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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-31

  • 博文量
    101
  • 访问量
    284337