Oracle Database Discussions

Hi All,

We have a materialized view on one system which pulls data from another system (both Oracle 11g) over a db link. However, it recently stopped working.

Any attempt to create a materialized view on the source table results in the following error:

ORA-02437: cannot validate (APNEW.XPKSTR_STORE1) - primary key violated

There is a primary key on the source table but it doesn't have the 1 at the end. There are no duplicates in the source table.

The query is (from dev system to production):

CREATE MATERIALIZED VIEW apnew.test_str
REFRESH FAST ON DEMAND AS
SELECT
prod_cd,
store_cd,
fcst_start_dt,
fcst_stop_dt,
pl_start_dt,
pl_stop_dt,
rade_cd,
store_grp_cd,
store_grp_nm,
do_not_override_ind,
last_mod_dt,
last_mod_userid
FROM apnew.str_store@D2P;

prod_cd and store_cd are the primary key in the source table.

Anyone have an idea what might be going on?

Thanks!
John