Online index problem – ORA-08104
12th March 2014 3 Comments
So, you’re creating (or rebuilding) an index ONLINE on a busy system. Your session dies, or it becomes necessary to kill the command, you may find that Oracle does not (always manage to) automatically clean up after itself.
CREATE INDEX my_ind ON my_table (mycol ASC) LOCAL LOGGING COMPRESS 1 ONLINE; (ctrl-c) ORA-01013: user requested cancel of current operation select * from user_indexes where index_name = 'my_ind'; INDEX_NAME INDEX_TYPE my_ind NORMAL
OMG! WTF! TLA’s! The index is there, even though I cancelled the create statement! Lets drop it…
drop index my_ind; * ERROR at line 1: ORA-08104: this index object 79722 is being online built or rebuilt
So, HOW do I sort out this mess? Use DBMS_REPAIR!
1 declare 2 lv_ret BOOLEAN; 3 begin 4 lv_ret := dbms_repair.online_index_clean(79722); 5* end; select * from user_indexes where index_name = 'ind_name'; no rows selected
Bang! and the index (or, rather, left-over temporary extents from the build attempt) is gone, ready for you to try again.