New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    699

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

ora-8104: this index object XXXX is being online built or rebuilt

Thanks to my friend (Siva Krishna) who updated me to post this in blog,

If session performing online index rebuild will be killed by mistake Oracle end up with some inconsistency inside dictionary.

If you try to re-run killed command to create index once more database will complain that index already exist.

Although if you try to drop index database will complain that index is in rebuild state and could not be dropped at that time.

To fix that issue you need to use package dbms_repair as showed in example:

Building test table

SQL> create table test tablespace users as select rownum id,

'xxxxxxxxxxxxxxxxxxxxxxxx' col1 from dba_source, dba_source where rownum < 10000000;

Table created.

New index build has been started with online clause and session has been killed

SQL> create index test_index on test (id) tablespace users online;
create index test_index on test (id) tablespace users online
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4356
Session ID: 191 Serial number: 13

Sanity check - index exist even if build has been never completed.

SQL> select index_name from dba_indexes where table_name = 'TEST';
INDEX_NAME
-------------------------
TEST_INDEX

Now let's try to drop it

SQL> drop index test_index;
drop index test_index
*
ERROR at line 1:
ORA-08104: this index object 66960 is being online built or rebuilt

So maybe we can create it again ?

SQL> create index test_index on test (id) tablespace users online;
create index test_index on test (id) tablespace users online
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Let's clean it up and allow Oracle to drop index - ONLINE_INDEX_CLEAN need a object number which can be taken from drop index error message or using the following query (at least it worked for me).

SQL> select min(object_id) from dba_objects where object_name = 'TEST_INDEX';
MIN(OBJECT_ID)
--------------------
66960
SQL> declare
ret boolean;
 begin
ret:=dbms_repair.ONLINE_INDEX_CLEAN(66960);
 end;
/
PL/SQL procedure successfully completed.

and now let's check if index is still there

SQL> select index_name from dba_indexes where table_name = 'TEST';
no rows selected

 

As I have used drop index, the case is the index has been dropped, if its alter index rebuilt session got killed, your index state would be normal, you can rebuilt later.

-Thanks

Geek DBA

January 16th, 2013 | Tags: , | Category: Administration

Comments are closed.