My Oracle Support Banner

With "_mv_refresh_use_stats"=true,commit SCN-based materialized view refresh fails with ORA-00904 (Doc ID 1367656.1)

Last updated on NOVEMBER 16, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

During fast refresh recursive query with HASH_SJ hint degrades
performance.To improve performance if "_mv_refresh_use_stats" is set to true,commit SCN-based materialized view fails with ORA-00904


SQL> create table t1 (id number, c1 varchar2(30), constraint t1_pk primary key(id));
SQL> create materialized view log on t1 with rowid, commit scn (c1);
SQL> create table t2 (id number, c1 varchar2(30), constraint t2_pk primary key (id));
SQL> create materialized view log on t2 with rowid, commit scn (c1);
SQL> create materialized view mv_t
refresh fast on demand
as
select t1.c1 t1_c1, t2.c1 t2_c1, t1.rowid t1_rowid, t2.rowid t2_rowid
from t1, t2
where t1.id = t2.id ;

SQL> insert into t1 values (1, 'xxx');
SQL> insert into t2 values (1, 'xxx');
SQL> commit;
SQL> alter session set "_mv_refresh_use_stats"=true;
SQL> exec dbms_mview.refresh('MV_T', 'f');
BEGIN dbms_mview.refresh('MV_T', 'f'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00904: "SNAPTIME$$": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1

Failing SQL:
>>
PARSE ERROR #6:len=132 dep=2 uid=64 oct=3 lid=64 tim=4099378009679 err=904
SELECT OLD_NEW$$, COUNT(*) FROM "APPS"."MLOG$_OE_ORDER_LINES_ALL1" WHERE
SNAPTIME$$ > :1 AND SNAPTIME$$ <= :2 GROUP BY OLD_NEW$$

SQL> insert into t1 values (2, 'xxx');
SQL> insert into t2 values (2, 'xxx');
SQL> alter session set "_mv_refresh_use_stats"=false;
SQL> exec dbms_mview.refresh('MV_T', 'f');
PL/SQL procedure successfully completed.

>>

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.