Hi ,
I have created an MV called TEST and it has been running fine for a while. only recently i got an error as follows :
ORA-12048: error encountered while refreshing materialized view "DD"."TEST"
ORA-00001: unique constraint (DD.TEST_PK) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
i created an mv log on TEST on the source db using the rowid & primary key option
from the source db's TEST , i did a distinct of the column(i.e TESTID) used for TEST_PK but there's no duplicates
i have a scheduled job to referesh this mv every 15th min
in the $Mlog_TEST , i could see there are duplicates though but is this the cause ? i.e within the next refresh , the same record was changed more than once so during MV refresh it violates the TEST_PK ?
TESTID M_ROW$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
1 AAAO/9ABBAAAiE0AA3 1/1/4000 U N FCFFFFFFFFFF
1 AAAO/9ABBAAAiE0AA5 1/1/4000 I N FEFFFFFFFFFF
1 AAAO/9ABBAAAiE0AA5 1/1/4000 U U FCFFFFFFFFFF
2 AAAO/9ABBAAAiE0AA5 1/1/4000 U N FCFFFFFFFFFF
2 AAAO/9ABBAAAiE0AA3 1/1/4000 I N FEFFFFFFFFFF
1 AAAO/9ABBAAAiE0AA3 1/1/4000 U U FCFFFFFFFFFF
my resolution was to disable the TEST_PK
pls advise where it might be wrong
tks & rgds