Re: Error while rebuilding a text index

From: zigzagdna <zigzagdna_at_yahoo.com>
Date: Thu, 10 Dec 2009 09:30:27 -0800 (PST)
Message-ID: <4d9d9bed-b897-4920-a457-245601784756_at_j4g2000yqe.googlegroups.com>

On Dec 10, 10:41 am, ddf <orat..._at_msn.com> wrote:
> Comments embedded.
> On Dec 9, 11:23 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
>
> > I am using Oracle 10.2.0.3 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

I had already read this confusing document a while ago. Text indexes seem different, when I enter:

alter index X_TEXT_ENTITY rebuild online;

I get syntax error (this is not the case with normal indexes).

alter index X_TEXT_ENTITY rebuild online *
ERROR at line 1:

ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX X_TEXT_ENTITY failed
DRG-10562: missing alter index parameter

If I use the syntax:

alter index X_TEXT_ENTITY rebuild online parameters ('RESUME');

There is no syntax error but it seems this statement does not do anything by looking at last analyzed in user indexes. Since my index did not really fail adding RESUME does not make sense anyway,

So one cannot rebuild text indexes "ONLINE", i.e., while index is being rebuilt one can do update/insert/delete on the table. Received on Thu Dec 10 2009 - 11:30:27 CST