alter index rebuild online

[mattd]

Any advice on how to rebuild indexes online? I am using oracle 8.1.6.1 which has a bug (1475310) that can cause corruption in the index. Metalink says:

Once an online rebuild has begun, any user transaction that updates the index will generate corrupt undo which is written to the rollback segment and redo log. If this corrupt undo is applied either due to process death or instance death, or following a point in time recovery, it will corrupt the index (or IOT). A user session rollback will not introduce the corruption.

Also I found that even with the online feature, Oracle still waits for user DML activity to complete before continuing with the index rebuild. This may cause the rebuild to take a long time.

Any advice or issues? Other ways to rebuild large indexes on a 24x7 active system?
Thanks.
- Matt

[pando]

Originally posted by mattd
Also I found that even with the online feature, Oracle still waits for user DML activity to complete before continuing with the index rebuild. This may cause the rebuild to take a long time.

eh, I thought DML is recorded in the journal table?

[marist89]

Originally posted by mattd

Any advice or issues? Other ways to rebuild large indexes on a 24x7 active system?

Upgrade to 8.1.7.4 or get a patch for your version.

[freedba]

If you suspect the index is corrupt, you can use

alter table xxx validate structure cascade;

to check the consistency, but this introduces a table lock and could take some time.

The problem can only be safely fixed by dropping and rebuilding the index. In fact, because Oracle is now capable of rebuilding one index by scanning another, the problem can propagate itself so that you may end up having to drop and recreate all the indexes on the table to clear the problem