I was trying to execute this DDL, on my hosted database, a 10.1.0.5 standard edition running on solaris:
alter index CONTEXT_STEM_IDX rebuild online;This statement is supposed to rebuild a context index online, that is without preventing users from issuing queries against this index while the operation is in progress.
ORA-00439: feature not enabled: Online Index Build
Unfortunately online index build will only work on an Enterprise Edition and a feature comparison list made by Don Burleson back in 2003, presumably for Oracle 9i, seems to support this interpretation.
I tried to locate an official Oracle feature map and indeed i found two, a detailed Oracle Product Feature by Edition and also a web page, but, unfortunately, i can't tell whether this optional feature is listed or not, the closest match i could spot is called parallel index build.
While searching for "feature comparison", i stumbled also on this interesting whitepaper concerning Oracle 10G R2 Online Data Reorganization & Redefinition where there is absolutely no mention of which database edition supports which feature.
If anyone can come up with something more precise on this subject, i'll most delighted to include a link to the document.
At any rate, a less demanding:
alter index CONTEXT_STEM_IDX rebuild;worked well and rebuilt my index in a few seconds.
If you can't afford to run the risk of blocking any users, you may want to consider rebuilding the index during off-peak hours, by running a scheduled job.
2 comments:
Probably this is because it is an context (domain) index. Context index is in fact set of tables that hold data for index.
Documentation for ALTER INDEX states that there are restriction for Rebuild clause on domain index:
* You can specify only the PARAMETERS clause (either for the index or for a partition of the index) or the parallel_clause. No other rebuild clauses are valid.
* You can rebuild an index only if the index is not marked IN_PROGRESS.
* You can rebuild an index partition only if the index is not marked IN_PROGRESS or FAILED and the partition is not marked IN_PROGRESS.
It explains this situation.
You are right in mentioning such restrictions, however i don't believe that this ORA-00439 message refers to them, it's a typical error issued in case of a missing option, i mean, it's raised while checking some prerequisite condition like "is this a standard edition or an enterprise edition?". Checking for parameter's value or an allowed combination of them seems to me a step further and probably you would get a different error message in the range ORA-29XXX, like ORA-29868 for instance. The documentation is a bit vague on this subject, sometimes it speaks of an index marked as "IN_PROGRESS" (some STATUS column i suppose), while in the error message book it seems to refer to a "LOADING" status, so i am wondering if i am looking at the same thing or not.
Anyway, i am going to see if i can set up a simple repeatable test to verify this.
Bye,
Flavio
Post a Comment