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

Last updated on MAY 12, 2017

Applies to:

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

Symptoms

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 ;
ALTER TABLE base DROP PARTITION p1;
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"

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