Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6935
  • Last Modified:

ORA-08104: this index object 36413 is being online built or rebuilt

Hi,

OS : Solaris 9
Oracle 9.2.0.5

My objective is to move an index from one tablespace to another.
      alter index <index name> rebuild tablespace <tablespace name>

It fails with this error :
ORA-08104: this index object 36413 is being online built or rebuilt

I'll have only one day tomorrow during which I can try dropping and building the index or other options. Not sure if it would allow me to drop.
Looking forward to expert advice so that I can try all available options during the time window provided.

TIA,
Rakesh
0
grora
Asked:
grora
1 Solution
 
BobMcCommented:
Has a previous attempt to online rebuild this index failed?
The error normally indicates that a rebuild (or recovery from a failed rebuild) is currently in progress.

You can either wait for the current operation to complete - V$session_longops may give some details of progress, or drop the index with "drop index <idxname> force;" and recreate it.

Heres a note I found from Oracle, but I wouldnt do this myself without checking with Oracle support first.

Goal
The client and shadow process that were performing a ALTER INDEX REBUILD ONLINE were killed. (kill -9) .

Attempts to drop the index returned ORA-8104 This index object %s is being online built or rebuilt.
Fix
The killed process that was performing the ALTER INDEX REBUILD ONLINE left the REBUILD FLAG
(bit 512) in the IND$ table.

This statement will show all of the indexes that are in the REBUILD ONLINE mode

Select obj# from ind$ where bitand(flags, 512)=512;

To fix the problem:
==============

Update ind$ set flags=flags-512 where obj#={object ID of the problem index};
commit;

Verify that the change was done:
========================

Select flags from ind$ where obj#={object ID of the problem index};

Now should be able to drop the index:
===========================

drop index {index name};
drop the SYS_JOURNAL_{obj ID}

If you still get an error on the drop, try:
============================

update ind$ set flags=0 where obj#={object ID of the problem index};
commit;
shutdown abort
startup
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now