Refresh MV with indexes set as unusable causes ORA-12801
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.
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.
0