Oracle Materialized View has duplicate entries

Viewed 28

I defined a matView for fast refresh by rowid and from time to time I got duplicated entries for the primary key. The master table definitely has no double entries. The view accesses a remote DB. I cannot refresh via primary key, because I need to have an outer join in case the referenced id is null. Most of the time it works fine, but every 1000 entries or so I get an entry twice. When I update this duplicated record in the master, the refresh of the view "repairs" the record and I have a single record. We have a RAC cluster with 2 instances.

create materialized view teststatussetat
refresh force on demand with rowid
as
select
    ctssa.uuid id,
    ctssa.uuid,
    ctssa.rowid ctssa_rowid,
    ctps.rowid ctps_rowid,
    ssf.rowid ssf_rowid,

    ctssa.coretestid coretest_uuid,
    ctssa.lastupdate,
    ctssa.pstatussetat statussetat,
    ctps.code status_code,
    ssf.account statussetfrom
from 
    coreteststatussetat@coredb ctssa,
    coretestprocessstatusrev@coredb ctps,
    coreuser@coredb ssf
where
    ssf.uuid(+) = ctssa.statussetfromid and
    ctps.uuid = ctssa.statusid
;

The log files are created like this:

create materialized view log on coreteststatussetat with sequence, rowid, primary key including new values;

We have an Oracle Database 19c Enterprise Edition Release 19.0.0.0.0.

To watch what happens I created a job which checks every 5 seconds, if the view contains duplicates for one day. The job found thousands of duplicate entries during the day but most of them (but not all) vanished away, so were there only temporary. The job protololled the primary key and also the rowid, so I hoped that I can find some changing rowids. But all of the duplicated primary keys have distinct rowids.

The data is created via Hibernate. But this should not make a difference. Oracle should not create duplicate entries.

0 Answers
Related