Solved

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

Posted on 2004-09-12
1
6,442 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
1 Comment
 
LVL 7

Accepted Solution

by:
BobMc earned 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

786 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