Re: Error while rebuilding a text index

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 10 Dec 2009 05:03:16 +0000 (UTC)
Message-ID: <hfpvek$qin$1_at_solani.org>

On Wed, 09 Dec 2009 20:23:18 -0800, zigzagdna 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;
> 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. Since I am rebuilding index online, this error should not
> occur, otherwise what
> Is the purpose of on line index rebuild? Can someone explain how to get
> rid
> o the error. I cannot stop my application while rebuilding text
> indexes.

Ah, Zigzag strikes again, with another case of RTFM. Why don't you do yourself a service and go to oracle-l? Steve Adams will gladly protect your feelings from the likes of me. You obviously don't know how to read the literature, so let me help you. Here it goes, from the 11.2 literature. Pay attention to the last paragraph in the quote.

<quote>
ALTER INDEX REBUILD Syntax

Use ALTER INDEX REBUILD to rebuild an index, rebuild an index partition, resume a failed operation, replace index metadata, add stopwords to an index, or add sections and stop sections to an index.

ALTER INDEX REBUILD has its own sub-syntax. That is, its parameters have their own syntax. For example, the ALTER INDEX REBUILD PARAMETERS command can take either REPLACE or RESUME as an argument, and ALTER INDEX REBUILD PARAMETERS ('REPLACE') has several arguments it can take.

Valid examples of ALTER INDEX REBUILD include the following statements:

ALTER INDEX REBUILD PARALLEL n

ALTER INDEX REBUILD PARAMETERS (SYNC memsize)
ALTER INDEX REBUILD PARAMETERS (REPLACE DATASTORE datastore_pref)
ALTER INDEX REBUILD PARAMETERS (REPLACE WORDLIST wordlist_pref)

This is the syntax for ALTER INDEX REBUILD:

ALTER INDEX [schema.]index REBUILD [PARTITION partname] [ONLINE] [PARAMETERS
(paramstring)][PARALLEL N] ;

PARTITION partname

    Rebuilds the index partition partname. Only one index partition can be built at a time.

    When you rebuild a partition you can specify only RESUME or REPLACE in paramstring. These operations work only on the partname you specify.

    With the REPLACE operation, you can only specify MEMORY and STORAGE for each index partition.

    Adding Partitions To add a partition to the base table, use the ALTER TABLE SQL statement. When you add a partition to an indexed table, Oracle Text automatically creates the metadata for the new index partition. The new index partition has the same name as the new table partition. Change the index partition name with ALTER INDEX RENAME.

    Splitting or Merging Partitions Splitting or merging a table partition with ALTER TABLE renders the index partition(s) invalid. You must rebuild them with ALTER INDEX REBUILD. [ONLINE]     ONLINE enables you to continue to perform updates, inserts, and deletes on a base table. It does not enable you to query the base table.

    Note:
    You can specify REPLACE or RESUME when rebuilding an index or an index partition ONLINE.
</quote>

The source is: Oracle® Text Reference 11g Release 2 (11.2) in the 1st chapter called "Oracle Text SQL Statements and Operators". This wasn't so hard, was it? The next step is to look in the documentation for yourself, it isn't that hard. Lastly, I used the version 11.2 documentation although I am pretty sure that your database isn't 11.2 because 11.2 has fewer limitations than the previous releases. If it was possible in the version 11.2, it might be possible in the version 10.2.0.4. As it isn't possible in the 11.2, it most certainly is not possible in any of the lower versions. Please, don't ask other people to read the documentation for you. You're almost grown up, you can do it yourself.

-- 
http://mgogala.freehostia.com

Received on Wed Dec 09 2009 - 23:03:16 CST