My Oracle Support Banner

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

Last updated on NOVEMBER 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
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 <MView_Name>;
drop table <Table_Name_1>;
drop table <Table_Name_2>;
Create table <Table_Name_2> (n number)
partition by range (n)
(partition p1 values less than (2),
partition p2 values less than (3));
Create table <Table_Name_1> as select 1 n from dual;
Create materialized view <MView_Name>
partition by range (n)
(partition p1 values less than (2))
enable query rewrite
as select t.n, 1 col2
from <Table_Name_2> t,
<Table_Name_2> t1,
<Table_Name_1> t1 ;
ALTER TABLE <Table_Name_2> DROP PARTITION p1;
exec dbms_mview.refresh('<MView_Name>','F');
*
ERROR at line 1:
ORA-32313: REFRESH FAST of "<Schema_Name>"."<MView_Name>" 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 "<Schema_Name>"."<MView_Name>"

Changes

 

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
Changes
Cause
Solution

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