Hi Guys
Here's an interesting one.
For the first time, I have created a fast refreshable, on commit materialized view. It works fine, except in one circumstance - if you perform an insert into the master table using a select that returns data from a DB Link, you get an ORA-02050 error:
insert into max_data.contract
SELECT *
FROM MAX_DATA.CONTRACT@livedb
WHERE CLNO = 'blah'
AND ACT = 'blah'
AND CSEQ = 'blah';
commit;
1 row created.
commit
Error at line 13
ORA-02050: transaction 7.13.1976742 rolled back, some remote DBs may be in-doubt
ORA-02051: another session or branch in same transaction failed or finalized
Once you have received this error, it is impossible to insert into the master table until the MView has been dropped. The insert then works and the MView can be recreated.
Normal insert statements work fine, as do updates and deletes.
I thought it might be to do with the MView Log which had EXCLUDE NEW VALUES, but I changed that to INCLUDE and it still fails.
Now, I know what you are going to say - "its in the error message, fool", except that the MView and the MView Log are in the same schema, in the same database as the master table, so it is not remote at all.
Has anyone seen this before?
I don't think this will be an issue in live as this form of insert only happens in test when we want to replicate some data.
We are using 11gR2 on Solaris.
Cheers
Simon