ORA-08104@ONLINE_INDEX_CLEAN
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完成清理動作後，再次查詢索引已經不存在。
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/，如需轉載，請註明出處，否則將追究法律責任。