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

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 test_snap
refresh complete
as select * from dept;

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

Atempt to refresh the materialized view directly:
exec dbms_mview.refresh('test_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       => '<group_name>',
   list       => '',
   next_date  => sysdate,
   interval   => 'sysdate+1');
end;


begin
 dbms_refresh.add(
  name  => '<group_name>',
  list  => 'test_snap');
end;

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

Changes

Cause