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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms