online创建索引中途取消导致索引无法删除解决办法

1.背景

有一个表ID栏位没有索引，但是在一个update语句的where中被使用，因此打算online创建索引，但是长时间没有成功，此时决定取消，取消后发现索引无法删除

2.问题还原

数据库监控报警有行锁，进入之后发现是一个update的sql迟迟没有提交，分析后发现走的是全表扫描

根据谓词条件创建索引

CREATE INDEX MONKEY.IX_TEST_ADDDATE ON MONKEY.TEST(ADD_DATE) TABLESPACE TBS ONLINE;

在创建的时候，一个200M的表执行了好久还是没有完成，此时查看发现被上边的updatg语句挡住了，此时查找资料后得知online可以在线加索引，但是如果表上面有DML语句一直不提交的话，就会挡着索引的创建，因此此时决定取消创建。

取消后，发现表上已经有索引了，个人感觉这个索引是不健康的，写了一个加hint的sql试了一下，这个索引果然没有起作用，因此决定删除重建。

删除索引时，有报错：ORA-00054: 資源正被使用中, 請設定 NOWAIT 來取得它, 否則逾時到期 。

3.问题解决

查看资料后，决定使用包DBMS_REPAIR手动清理 。

-- 查找索引的object_id
SELECT i.obj#,
       i.flags,
       u.name,
       o.name,
       o.type#
  FROM sys.obj$ o, sys.user$ u, sys.ind_online$ i
 WHERE     (BITAND (i.flags, 256) = 256 OR BITAND (i.flags, 512) = 512)
       AND (NOT ( (i.type# = 9) AND BITAND (i.flags, 8) = 8))
       AND o.obj# = i.obj#
       AND o.owner# = u.user#;

-- 或者使用dba_objects视图
-- 使用如下sql以SYS用户进行清理
/* Formatted on 2024/5/23 上午 10:49:23 (QP5 v5.163.1008.3004) */
DECLARE
   isClean   BOOLEAN;
BEGIN
   isClean := FALSE;

   WHILE isClean = FALSE
   LOOP
      isClean :=
         DBMS_REPAIR.online_index_clean (200053, DBMS_REPAIR.lock_wait);
      DBMS_LOCK.sleep (2);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;
/

或者直接不找object_id，直接清理

/* Formatted on 2024/5/23 上午 10:39:45 (QP5 v5.163.1008.3004) */
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;
/

手动清理的过程中，发现sql一直迟迟执行不了，查找资料发现，这个语句会一直试图占用资源，但是如果一直无法占用资源，这个sql就会被挡。到这里继续找表上的迟迟不提交DML，找到并杀掉进程后，顺利执行，执行后索引就没有了。