Re: Error while rebuilding a text index
From: ddf <oratune_at_msn.com>
Date: Thu, 10 Dec 2009 10:32:10 -0800 (PST)
Message-ID: <00d8cb9a-c597-47c2-adc5-e32418363d61_at_u25g2000prh.googlegroups.com>
On Dec 10, 12:30 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 10 Dec 2009 10:32:10 -0800 (PST)
Message-ID: <00d8cb9a-c597-47c2-adc5-e32418363d61_at_u25g2000prh.googlegroups.com>
On Dec 10, 12:30 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Possibly you should consider using REPLACE rather than RESUME since you're not resuming a prior index rebuild. Or maybe you should read the document Joel provided.
David Fitzjarrell Received on Thu Dec 10 2009 - 12:32:10 CST