Hi Tom,
first of all, thanks for your great posts on different topics of Oracle.
1.i understand that this error happens on the long running queries(while the accessed rollback segments are overwritten). But why this error occurs during materialized view refresh(long running)?
2.i asked the above question, because, i have set the undo_retention to 23000 and undo tablespace size to 7gb
as per the guidelines in the below link, but i still get this error(snapshot too old error) :
http://www.akadia.com/services/ora_optimize_undo.html the below is the exact error message, i am getting
BEGIN DBMS_MVIEW.REFRESH('test_info_mv2','C'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$"
too small
ORA-02063: preceding line from OPRD.DSTB.NET
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 1
do i still need to increase the undo_retention and undo tablespace size?
thanks in advance.
regards
September 26, 2012 - 12:07 pm UTC
1) because there would be a long running query in a long running refresh.
2) so either the undo retention is too low (does the fresh take longer than ~6 hours?) or 7gb is insufficient to hold the amount of undo you generate (are you looking to see if we are having to expire extents prematurely? look in v$undostat)
you need to decide if undo retention is long enough (6 hours - is that long enough?) and if 7gb is sufficient to hold 6 hours worth (are we expiring extents prematurely?)