LATEST TOPICS

ORA-01555 from 11g target database while refreshing MVIEW

Symptom:

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.

For Example:

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'declare dummy char(1);
begin
----
---- include the workaround just before the MVIEW refresh statement
----
select * into dummy from dual@[dblink_pointing_to_master_site];
----
---- Mview refresh statement
----
dbms_mview.refresh('"[schema]"."[mview_name]"');
end;
'
,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));
COMMIT;
END;

 

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed