ORA-08104报错处理

ORA-08104报错处理 参考文档： Customer RecommendedHow to Cleanup and Rebuild an Interrupted Online Index Rebuild - ORA-8104 , ORA-8106 (Doc ID 272735.1) Drop Partition Failed With ORA-08104: This Index Object Is Being Online Built Or Rebuilt (Doc ID 2358693.1) ORA-08104介绍： [19c@test bin]$ oerr ora 08104 08104, 00000, "this index object %s is being online built or rebuilt" // *Cause:  the index is being created or rebuild or waited for recovering  //          from the online (re)build  // *Action: wait the online index build or recovery to complete 报错原理： 在进行online rebuild | create 时，Oracle 会修改如下信息： 修改ind$中索引的flags，将该flags+512. 关于flags的含义，在下面进行说明。 在该用户下创建一个临时日志表 （表名为sys_journal_<object_id>）来保存在创建或者重建索引期间产生的日志信息。 如果操作异常结束，而Oracle的SMON进程还没来得及清理journal table和ind$的flags标志位，系统会认为online (re)build操作还在执行。 因此在drop索引时会报错 this index object %s is being online built or rebuilt 关于ind$中的flags字段 Flag字段的说明可以在ind$的sql.bsq脚本中找到： /* mutable flags: anything permanent should go into property */ /* unusable (dls) : 0x01 */ /* analyzed : 0x02 */ /* no logging : 0x04 */ /* index is currently being built : 0x08 */ /* index creation was incomplete : 0x10 */ /* key compression enabled : 0x20 */ /* user-specified stats : 0x40 */ /* secondary index on IOT : 0x80 */ /* index is being online built : 0x100 */ /* index is being online rebuilt : 0x200 */ /* index is disabled : 0x400 */ /* global stats : 0x800 */ /* fake index(internal) : 0x1000 */ /* index on UROWID column(s) : 0x2000 */ /* index with large key : 0x4000 */ /* move partitioned rows in base table : 0x8000 */ /* index usage monitoring enabled : 0x10000 */ 异常终止的情况下，可以发现ind$关于该索引的状态还是online rebuild的。 解决方法： 1. 等待SMON进程清理 根据上面的原理，如果不着急的话，可以等待SMON进程自己去清理 2. 手动清理 首先查询问题索引的object_id select object_id from dba_objects where object_name='问题索引名字';   执行下述存储过程进行优化 declare   isClean boolean; begin   isClean := FALSE;   while isClean=FALSE loop     isClean := dbms_repair.online_index_clean(object_id,dbms_repair.lock_wait);     dbms_lock.sleep(2);   end loop;   exception      when others then        RAISE;  end; / declare done boolean; begin    done:=dbms_repair.online_index_clean(275314);  end; / 或者 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; / dbms_repair.lock_wait 表示不断寻找资源锁，直到抢到为止（如果一直占不到锁，可能会运行非常长时间）也可以指定 dbms_repair.all_index_id 清理所有问题索引 存储过程 官方介绍： This function performs a manual cleanup of failed or interrupted online index builds or rebuilds.  This action is also performed periodically by SMON, regardless of user-initiated cleanup. This function returns TRUE if all indexes specified were cleaned up and FALSE if one or more indexes could not be cleaned up. Syntax： DBMS_REPAIR.ONLINE_INDEX_CLEAN (    object_id      IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,    wait_for_lock  IN BINARY_INTEGER DEFAULT LOCK_WAIT)  RETURN BOOLEAN; Parameters ONLINE_INDEX_CLEAN Function Parameters Parameter      Description object_id        Object id of index to be cleaned up. The default cleans up all object ids that qualify. wait_for_lock    This parameter specifies whether to try getting DML locks on underlying table [[sub]partition] object.                   The default retries up to an internal retry limit, after which the lock get will give up.  If LOCK_NOWAIT is specified, then the lock get does not retry.