While transferring data from materialized view site end. I was faced the below error message.
ORA-12012: error on auto execute of job 2
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Oracle suggest to fix this error automatically using “refresh_after_errors= true”
MviewAdmin@Materialized view site.
name => '"MVIEWADMIN"."MIS_REFG"',
next_date => to_date('
interval => 'SYSDATE + 1 + (1/24)',
implicit_destroy => FALSE,
rollback_seg => 'NULL',
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => 1,
parallelism => 0,
heap_size => 0);
By logically it’s not good. You should be find why data’s not transfer? Any problem in your database?
Here you can find out those problems using the following data dictionary views,
SQL> select deferred_tran_id, destination, error_number, error_msg from deferror;
Using deferred transaction id, you can find out which transaction not updated in materialized view site. Fix those errors. After that only transfer your data’s.
Please post your comments.