Re: Error while rebuilding a text index

On Dec 10, 10:41 am, ddf <orat...@xxxxxxx> wrote:
Comments embedded.
On Dec 9, 11:23 pm, zigzagdna <zigzag...@xxxxxxxxx> 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.
.

Relevant Pages

  • Re: Error while rebuilding a text index
    ... Alter index rebuild textidx1; ... Oracle you wanted to do that by using the ONLINE directive in the ... ALTER INDEX statement: ... during a rebuild, online or not. ...
    (comp.databases.oracle.server)
  • Re: DBReindex
    ... dbcc dbreindex routine, I tried to update the dbcc dbreindex command with ... I assume there is a way to rebuild indexes in SQL 2005 but for me it isn't ... Bulk-logged recovery still gives the possibiliy ... You can also consider ALTER INDEX REORGANIZE this command can be ...
    (microsoft.public.sqlserver.security)
  • Re: Error while rebuilding a text index
    ... Alter index rebuild textidx1; ... Sorry, no, you're not rebuilding this index online as you never told ... Alter index rebuild textidx1 online; ...
    (comp.databases.oracle.server)
  • Re: Resize TableSpace (Index)
    ... should have been 'alter index xxx rebuild TABLESPACE TEMP;' ... to coalesce all free space ...
    (comp.databases.oracle.misc)
  • Re: index in Yukon
    ... There are some extras too - with ALTER INDEX you can rebuild an index online ... Microsoft SQL Server Storage Engine ... > Same as between DBCC DBREINDEX and DBCC INDEXDEFRAG. ...
    (microsoft.public.sqlserver.server)