Re: Error while rebuilding a text index

From: ddf <>
Date: Thu, 10 Dec 2009 07:41:40 -0800 (PST)
Message-ID: <>

Comments embedded.
On Dec 9, 11:23 pm, zigzagdna <> wrote:
> I am using Oracle on HP UNIX 11i. I am rebuilding a text
> index (textidx1) online
> using:
> Alter index rebuild textidx1;

Sorry, no, you're not rebuilding this index online as you never told Oracle you wanted to do that by using the ONLINE directive in the ALTER INDEX statement:

Alter index rebuild textidx1 online;

> If I do a query so index is used, I get:
> ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
> Error goes away after index is completely rebuilt.

And it should; ONLINE rebuilds only allow DML on the base table, not queries OF the base table. The documentation clearly states this.

> Since I am rebuilding index online, this error should not occur,

You're not rebuilding online, and even if you were the error WILL still occur because queries against the base table are not permitted during a rebuild, online or not.

> otherwise what
> Is the purpose of on line index rebuild?

It allows OLTP applications to continue to insert/update/delete data in the base table so people can continue working.

>Can someone explain how to
> get rid
> o the error.

Stop trying to query the base table while the rebuild is in progress.

> I cannot  stop my application while rebuilding text
> indexes.

And you don't have to stop your application, you simply need to learn how to actually do an online index rebuild and stop trying to query the base table while the rebuild is occurring.

David Fitzjarrell Received on Thu Dec 10 2009 - 09:41:40 CST

Original text of this message