MVIEW refresh is getting failed with following errors.
ORA-12012: error on auto execute of job 31030
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small
ORA-02063: preceding line from XXXXX
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Querying the targets database showed target database has enough UNDO space available.
Querying the V_$UNDOSTAT on target side also revealed that the query length was far less than the UNDO retention.
After researching a while in Oracle Metalink, found the issue as a Bug 611416 which is not feasible to fix.
There is a simple workaround to fix the issue as per Oracle Support.
include a simple “select * from dual@[dblink_pointing_to_master_site];” just prior to the refresh.
( job => X
,what => 'declare dummy char(1);
---- include the workaround just before the MVIEW refresh statement
select * into dummy from dual@[dblink_pointing_to_master_site];
---- Mview refresh statement
,next_date => to_date('25.06.2014 12:09:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC (SYSDATE,''HH24'')+369/1440'
,no_parse => FALSE
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));