Online index problem – ORA-08104

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)

ORA-01013: user requested cancel of current operation

select * from user_indexes where index_name = 'my_ind';

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.


4 Responses to Online index problem – ORA-08104

  1. Dom Brooks says:

    Useful little titbit – thanks.


    • Sometimes my blog is just for my own notes. I haven’t used DBMS_REPAIR in 20 years, and this should speed my google search up if I hit this (unexpected) problem again.


      • Dom Brooks says:

        This sort of thing highlights the value of blogging though.
        I probably won’t remember the full context of problem/resolution but if I hit the issue then I will get a vague sense of familiarity and maybe remember either a connection with your blog or maybe something to do with dbms_repair.


  2. Pingback: Online Index Rebuild Problem in 12C | Neil Chandler's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.