Complete refresh of a materialized view fails with ORA-02266 (Doc ID 270456.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 9.2.0.1 - Release: 8.1.7 to 9.2
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2.0   [Release: 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 3-Aug-2011 ***

Symptoms

This problem occurs if the following points are true :

1. The materialized view is performing a COMPLETE refresh.
2. The primary key of the materialized view is being referenced in a foreign key constraint
3. The foriegn key constraint is intially deferred and deferrable.

The following example indicates how the problem might arise:

alter table dept add primary key(deptno);

create snapshot log on dept;

create snapshot dept_snap
refresh complete
as select * from dept;

alter table emp add constraint emp_fk
foreign key(deptno)
references dept_snap(deptno)
initially deferred deferrable;

Atempt to refresh the materialized view directly:
exec dbms_mview.refresh('dept_snap')
This fails with : ORA-12008: error in snapshot refresh path
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Attempt to refresh the materialized view through a refresh group:

begin
 dbms_refresh.make(
   name       => 'ref_grp',
   list       => '',
   next_date  => sysdate,
   interval   => 'sysdate+1');
end;

begin dbms_refresh.add( name => 'ref_grp', list => 'dept_snap'); end;

exec dbms_refresh.refresh('ref_grp')
fails with the same error :
ORA-12008: error in snapshot refresh path
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

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