ORA-12008: error in materialized view refresh path

ra-32690 During Complete Refresh Of Materialized View (Doc ID 761803.1)

 

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
*** Checked for relevance on 20-FEB-2011 ***

Goal

When a materialized view is refreshed the following errors are raised:
SQL> @DWH_MV_PSG_DEC_COGNOS_B.sql
BEGIN dbms_mview.refresh(list => 'DWH_MV_PSG_DEC_COGNOS_B', method => 'C',atomic_refresh
=> false); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P000
ORA-32690: Hash Table Infrastructure ran out of memory
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1

Fix

The error is most likely caused by unpublished bug "Bug 6471770 - PARALLEL SERVERS DIE UNEXPECTEDLY RESULTING IN FAILURE OF YAHOO DW QUERIES," which is fixed in 11.1.0.7, 11.2.0.1, the 10.2.0.5 patch set, and a Windows patch bundle for both 10.2.0.3 and 10.2.0.4.   This error can be seen when operating on a large volume of data if hash group by aggregation is used.  It is possible for hash group-bys to over allocate memory beyond the limit imposed by the auto-memory manager because of this bug.

You can do one of the following and then rerun the mview refresh to work around the problem.
a.)
sql>alter session set "_gby_hash_aggregation_enabled"=false;

b.)
use the no_use_hash_aggregation hint.