Database DataWarehousing (MOSC)

MOSC Banner

Refresh MV with indexes set as unusable causes ORA-12801

edited Sep 18, 2011 11:32PM in Database DataWarehousing (MOSC) 2 commentsAnswered
We are trying to maximumize the speed that some of our MV are being refreshed.
We are attempting the following:

1. Alter MV parallel
2. Alter indexes on MV as parallel
3. Alter indexes on MV as unusable
3. Refresh MV with atomic_refresh=false

In some cases this works fine, in others the error below is generated:

ORA-12801: error signaled in parallel query server P018, instance
ORA-02097: parameter cannot bemodified because specified value is invalid

From what I notice so far, it works fine on MV that have only one index.  MV that have multiple indexes are the ones that seem to fail - this is even if the indexes are all non-unique.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center