ORA-08104 troubleshooting

Today, the development database has created an index online for more than one hour. An error is returned when an index is deleted after it is forcibly canceled.

Re-create the index will tell you that the index already exists, drop index will tell you that the index is locked, or the ORA-08104 (this index object xxxxx is being online built or rebuilt) error.

Some temporary objects created before this process fails are cleared by SMON. Unfortunately, SMON may not be used. -- I observed it for 2 hours on 9i (cycle ?) .

In version 10, the DBMS_REPARE package of Oracle is added with online_index_clean, which can be manually cleared:

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;

SQL> declare
2 done boolean;
3 begin
4 done: = dbms_repair.online_index_clean (194884 );
5 end;
6/
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00. 43
 
The index is deleted again.