SMON的作用还包括清理IND$字典基表(cleanup ind$):
触发场景 当我们在线创建或重建索引时(create or rebuild index online)，服务进程会到IND$字典基表中将该索引对应的记录的FLAGS字段修改为十进制的256或者512(见上图0x100=256,0x200=512)，如:
SQL> create index macleans_index on larges(owner,object_name) online;SQL> select obj# from obj$ where name='MACLEANS_INDEX'; OBJ#---------- 1343842SQL> select FLAGS from ind$ where obj#=1343842; FLAGS---------- 256ind_online$字典基表记录了索引在线创建/重建的历史SQL> select * from ind_online$; OBJ# TYPE# FLAGS---------- ---------- ---------- 1343839 1 256 1343842 1 256create table ind_online$( obj# number not null, type# number not null, /* what kind of index is this? */ /* normal : 1 */ /* bitmap : 2 */ /* cluster : 3 */ /* iot - top : 4 */ /* iot - nested : 5 */ /* secondary : 6 */ /* ansi : 7 */ /* lob : 8 */ /* cooperative index method : 9 */ flags number not null /* index is being online built : 0x100 */ /* index is being online rebuilt : 0x200 */)
原则上online create/rebuild index的的清理工作由实际操作的服务进程负责完成，这种清理在DDL语句成功的情况下包括一系列数据字典的维护，在该DDL语句失败的情形中包括 和数据字典的维护，无论如何都需要drop在线日志中间表
SYS_JOURNAL_nnnnn(nnnn为该索引的obj#)。数据字典的维护工作就包含对IND$基表中相应索引记录的FLAGS标志位的恢复，但是如果服务进程在语句执行过程中意外终止的话，那么短时间内FLAGS标志位字段就无法得到恢复，这将导致对该索引的后续操作因ORA-8104错误而无法继续:
SQL> drop index macleans_index;drop index macleans_index *ERROR at line 1:ORA-08104: this index object 1343842 is being online built or rebuilt08104, 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
SMON负责在启动后(startup)的每小时执行一次对IND$基表中因在线创建/重建索引失败所留下记录的清理，这种清理工作由kdicclean函数驱动(kdicclean is run by smon every 1 hour，called from SMON to find if there is any online builder death and cleanup our ind$ and obj$ and drop the journal table, stop journaling)。 这种清理工作典型的调用堆栈stack call如下:
ksbrdp -> ktmSmonMain ktmmon -> kdicclean -> kdic_cleanup -> ktssdrp_segment
注意因为SMON进程的清理工作每小时才执行一次，而且在工作负载很高的情况下可能实际很久都不会得到清理，在这种情景中我们总是希望能尽快完成对索引的在线创建或重建，在10gr2以后的版本中我们可以直接使用dbms_repair.online_index_clean来手动清理online index rebuild的遗留问题:
SQL> drop index macleans_index;drop index macleans_index *ERROR at line 1:ORA-08104: this index object 1343842 is being online built or rebuiltDECLARE 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;END;/SQL> drop index macleans_index; drop index macleans_index *ERROR at line 1:ORA-01418: specified index does not exist成功清理
但是如果在9i中的话就比较麻烦，可以尝试用以下方法(不是很推荐，除非你已经等了很久):
1.首先手工删除在线日志表，通过以下手段找出这个中间表的名字select object_name from dba_objects where object_name like (select '%' || object_id || '%' from dba_objects where object_name = '&INDEX_NAME')/Enter value for index_name: MACLEANS_INDEXold 6: where object_name = '&INDEX_NAME')new 6: where object_name = 'MACLEANS_INDEX')OBJECT_NAME--------------------------------------------------------------------------------SYS_JOURNAL_1343845SQL> drop table SYS_JOURNAL_1343845;Table dropped.2.第二步要手动修改IND$字典基表!!!!!! 注意！手动修改数据字典要足够小心！！select flags from ind$ where obj#=&INDEX_OBJECT_ID;Enter value for index_object_id: 1343845old 1: select flags from ind$ where obj#=&INDEX_OBJECT_IDnew 1: select flags from ind$ where obj#=1343845 FLAGS---------- 256a) 针对online create index，手动删除对应的记录delete from IND$ where obj#=&INDEX_OBJECT_IDb) 针对online rebuild index，手动恢复对应记录的FLAGS标志位update IND$ set FLAGS=FLAGS-512 where obj#=&INDEX_OBJECT_ID
接下来我们实际观察一下清理工作的细节:
SQL> select obj# from obj$ where name='MACLEANS_INDEX'; OBJ#---------- 1343854SQL> select FLAGS from ind$ where obj#=1343854; FLAGS---------- 256SQL> oradebug setmypid;Statement processed.SQL> oradebug event 10046 trace name context forever,level 8;Statement processed.SQL> DECLARE 2 isClean BOOLEAN; 3 BEGIN 4 isClean := FALSE; 5 WHILE isClean=FALSE 6 LOOP 7 isClean := dbms_repair.online_index_clean( 8 dbms_repair.all_index_id, dbms_repair.lock_wait); 9 10 dbms_lock.sleep(10); 11 END LOOP; 12 END; 13 /PL/SQL procedure successfully completed.===============================10046 trace=============================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#select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property, 1024), 0, 0, 1) from ind$ i, obj$ o, user$ u where i.obj# = :1 and o.obj# = i.bo# and o.owner# = u.user#delete from object_usage where obj# in (select a.obj# from object_usage a, ind$ b where a.obj# = b.obj# and b.bo# = :1)drop table "SYS"."SYS_JOURNAL_1343854" purgedelete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)delete from ind$ where bo#=:1delete from ind$ where obj#=:1
我们可以利用以下语句找出系统中可能需要恢复的IND$记录，注意不要看到查询有结果就认为这是操作失败的征兆，很可能是有人在线创建或重建索引:
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#/
相关诊断事件可以通过设置诊断事件event=’8105 trace name context forever' 来禁止SMON清理IND$(Oracle event to turn off smon cleanup for online index build)
alter system set events '8105 trace name context forever';