My Oracle Support Banner

Self-Join Is Not Supported With Pct (Doc ID 461699.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 9.2 to 11.2
Information in this document applies to any platform.


Can not do a fast refresh on an mview, after doing a maintenance operation on a partition such as deleting a partition of one of the base tables.
dbms_mview.explain_mv does not report that.

drop materialized view mv;
drop table load;
drop table base;
Create table base (n number)
partition by range (n)
(partition p1 values less than (2),
partition p2 values less than (3));
Create table load as select 1 n from dual;
Create materialized view mv
partition by range (n)
(partition p1 values less than (2))
enable query rewrite
as select t.n, 1 c2
from base t,
base t1,
load t1 ;
exec dbms_mview.refresh('mv','F');
ERROR at line 1:
ORA-32313: REFRESH FAST of "MARS"."MV" unsupported after PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 1

Note that in 9i you will see different message:

ORA-12004: REFRESH FAST cannot be used for materialized view "MARS"."MV"


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

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