[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-09-12
1
Medium Priority
?
6,770 Views
Last Modified: 2013-12-11
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
Comment
Question by:grora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
BobMc earned 2000 total points
ID: 12039421
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question