Re: Error while rebuilding a text index

On Dec 10, 12:30 pm, zigzagdna <zigzag...@xxxxxxxxx> wrote:
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.- 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
.

Relevant Pages

  • 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: 9i creating indexes with the ONLINE option
    ... in 9iR2 specifying the ONLINE option? ... This means the rebuild has to wait for all current ... Once current sessions complete, the rebuild starts ... lock - waits for current transactions on the table to ...
    (comp.databases.oracle.server)
  • 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: defrag of 101G table, What is the best way?
    ... window And a 24X7 application on top of this database. ... What version and edition of SQL Server do you have? ... If you have SQL 2005/2008 Enterprise, you can use the WITH ONLINE option ... with ALTER INDEX REBUILD to make an online rebuild. ...
    (comp.databases.ms-sqlserver)
  • 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)