今天在外干活，遇到Online重建索引，取消又重建遇到ORA-08104错误。
查看MOS直接说明此错就是因为online重建索引又取消后再次重建会报的错。
因客户不允许拿出原处理过程文字，以下仅将处理过程作为说明记录：
要解决此问题，会使用到 DBMS_REPAIR.ONLINE_INDEX_CLEAN 函数：
解决过程：
使用 DBMS_REPAIR.ONLINE_INDEX_CLEAN 函数：
参数说明：
object_id :
ALL_INDEX_ID (= 0) : 清除所有符合条件的索引对象
ALL_INDEX_ID (> 0) : 清理指定索引对象
wait_for_lock : LOCK_WAIT=1 : 重试获取基础表 [[sub]partition] 对象上的 DML 锁。
有内部重试限制，超过这个限制就会放弃lock get
LOCK_NOWAIT=0：不重试
函数需要的object id来自报错提示:
ORA-08104: this index object ##### is being online built or rebuilt
函数：
SET SERVEROUTPUT ON
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(10);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
示例
索引object_id=88214：
declare
done boolean;
begin
done:=dbms_repair.online_index_clean(88214);
end;
/
https://www.anbob.com/archives/2509.html：
Tip:
Online index rebuilds which fail (for any reason) leave the dictionary marked that the rebuild was in progress and SMON should
clean up the dictionary (kdicclean). This cleanup function is only executed every hour by SMON so you have to wait for SMON to clean IND$.
Of course, we can also manually clean up, To resolve this issue you should refer to the following method run the rebuild using DBMS_REPAIR.ONLINE_INDEX_CLEAN function:
Note:
If rebuilding the index without online option, Oracle will grab the index in X-mode and rebuild a new index segment by selecting the data from the old index. So here we are
– not allowing any DML on the table hence there is no journal table involved
– and it is doing an index scan
So if you do lots of DML on the same table,while rebuilding index online,it should take longer time.
借鉴文档：
https://www.anbob.com/archives/2509.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REPAIR.html#GUID-05BE0402-6B4F-479E-8B91-3E3031E0CBF5
E-Business Suite Applications Technology Stack Database Administration Steps To Use DBMS_REPAIR.ONLINE_INDEX_CLEAN For The Cleanup Of Online Index Failing With Error ‘ORA-08104 this index object is being online built or rebuilt’ (Doc ID 1378173.1)
