ORA-08104@ONLINE_INDEX_CLEAN

yyp2009發表於2013-12-03
Index
ps -ef |grep ora* lwc -l


ora-08104




select min(object_id) from dba_objects where object_name ='ECS_ORDER_TRACE' 


select index_name from dba_indexes where table_name ='ECS_ORDER_TRACE';


429471


declare
 ret boolean;
 begin
 ret:=sys.dbms_repair.ONLINE_INDEX_CLEAN(OBJECT_ID=>429471);
commit;
end;
 /




SQL> 
SQL> declare
  2    isclean boolean;
  3   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;
  4    5    6    7    8    9   10   end;
 /
 
 11  






PL/SQL procedure successfully completed.


SQL> SQL> SQL> SQL> SQL> 




SQL> select min(object_id) from dba_objects where object_name ='ECS_ORDER_TRACE' ;


MIN(OBJECT_ID)
--------------
        429471


SQL> declare
  2   ret boolean;
  3   begin
 ret:=dbms_repair.ONLINE_INDEX_CLEAN(429471);
end;
 /  4    5    6  
declare
*
ERROR at line 1:
ORA-24120: invalid OBJECT_ID parameter passed to DBMS_REPAIR.ONLINE_INDEX_CLEAN
procedure
ORA-06512: at "SYS.DBMS_REPAIR", line 475
ORA-06512: at line 4




SQL> 
SQL> select index_name from dba_indexes where table_name ='ECS_ORDER_TRACE';


INDEX_NAME
------------------------------
IDX_EOT_SEQUENCE
IDX_EOT_EXT_CUST_ORDER_ID
IDX_ECS_ORDER_TRACE_01
IDX_EOT_STATE_TRANSACTION_ID
IDX_ECS_ORDER_TRACE_EOID


select obj#,flags from ind$ where obj#=
 select obj#,flags from ind$ where obj#=429471;




select obj#,flags from ind$ where obj#=240940; OBJ# FLAGS ---------- ---------- 240940 256




Flag欄位的說明可以在ind$的sql.bsq指令碼中找到：


/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0×01 */
/* analyzed : 0×02 */
/* no logging : 0×04 */
/* index is currently being built : 0×08 */
/* index creation was incomplete : 0×10 */
/* key compression enabled : 0×20 */
/* user-specified stats : 0×40 */
/* secondary index on IOT : 0×80 */
/* index is being online built : 0×100 */
/* index is being online rebuilt : 0×200 */
/* index is disabled : 0×400 */
/* global stats : 0×800 */
/* fake index(internal) : 0×1000 */
/* index on UROWID column(s) : 0×2000 */
/* index with large key : 0×4000 */
/* move partitioned rows in base table : 0×8000 */
/* index usage monitoring enabled : 0×10000 */
在SMON完成清理動作後，再次查詢索引已經不存在。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-1062074/，如需轉載，請註明出處，否則將追究法律責任。