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)
 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 <B>79722</B> 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.

About these ads

3 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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 26 other followers

%d bloggers like this: