create index online 异常处理 ORA-08104

最新推荐文章于 2024-06-01 07:26:59 发布

EVISWANG

最新推荐文章于 2024-06-01 07:26:59 发布

阅读量1.1k

点赞数

分类专栏： Index

本文链接：https://blog.csdn.net/EVISWANG/article/details/53391885

版权

Index 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

开发online加了条索引，等了半小时没结果手动CRTL+C了，没起作用直接把窗口关了

SQL>  drop index clspuser.SPLIT_RSLT_HB_STATUS_IDX ;
 drop index clspuser.SPLIT_RSLT_HB_STATUS_IDX
                     *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> alter index   clspuser.SPLIT_RSLT_HB_STATUS_IDX  rebuild;
alter index   clspuser.SPLIT_RSLT_HB_STATUS_IDX  rebuild
*
ERROR at line 1:
ORA-08104: this index object 184546 is being online built or rebuilt


SQL>

这个网上有很多血案：幸好未酿成大错

手工执行如下清理job：

SQL> DECLARE
  2  isClean BOOLEAN;
  3  BEGIN
  4    isClean := FALSE;
  5    WHILE isClean=FALSE
  6    LOOP
  7      isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id, dbms_repair.lock_wait);
  8      dbms_lock.sleep(5);
  9    END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>

清理完成后显示索引不存在了：

SQL> select index_name, column_name,column_position from user_ind_columns where table_name ='CRF_P2P_INTEREST_SPLIT_RSLT' order by index_name, column_position;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IDX_INTEREST_SPLIT_RSLT_CPIISM CPIS_PRI_NUMBER                              1
IDX_INTEREST_SPLIT_RSLT_CPISM  CPISM_PRI_NUMBER                             1
PK_CRF_P2P_INTEREST_SPLIT_RSLT PRI_NUMBER                                   1

SQL>

再重新创建：

SQL> create index SPLIT_RSLT_HB_STATUS_IDX ON  clspuser.crf_p2p_interest_split_rslt (HB_STATUS) ;
create index SPLIT_RSLT_HB_STATUS_IDX ON  clspuser.crf_p2p_interest_split_rslt (HB_STATUS)
                                                   *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> alter  index SPLIT_RSLT_HB_STATUS_IDX  rebuild;
alter  index SPLIT_RSLT_HB_STATUS_IDX  rebuild
*
ERROR at line 1:
ORA-01418: specified index does not exist


SQL>  create index SPLIT_RSLT_HB_STATUS_IDX ON  clspuser.crf_p2p_interest_split_rslt (HB_STATUS) online;

Index created.

SQL>