ORA-00001: unique constraint

Hello,

Today I got below error message in my replication env.

ORA-00001: unique constraint

As per my investication

1/ There is NO duplicate records in my base table & materialized view.

In my master & materialized view site having some database trigger to generate auto-increment values using trigger.

As per my trigger from mview site some values generated; the same values generated from master site.

That is the reason implemented DBMS_MVIEW.I_AM_A_REFRESH package from mview site.

For Example:

Master Site

SQL> create table tst_tri (a int);
Table created.

SQL> create or replace trigger tst_tri_after after insert on tst_tri
2 begin
3 delete from tst_tri;
4 end ;
5 /
Trigger created.

SQL> insert into tst_tri values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tst_tri;

no rows selected

From Materialized View site:

SQL> create or replace trigger tst_tri_after after insert on babu.tst_tri
2 begin
3 if dbms_mview.i_am_a_refresh then
4 delete from babu.tst_tri;
5 end if;
6 end;
7 /

Trigger created.


SQL> select * from babu.tst_tri;

no rows selected

SQL> insert into babu.tst_tri values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from babu.tst_tri;

A
----------
1

SQL> drop trigger sys.tst_tri_after;

Trigger dropped.