When executing a Materialized View refresh an ORA-01555 is returned. This returns the following type of error either in the alert.log :
ERROR at line 1:
ORA-12048: error encountered while refreshing materialized view "<mview_owner>"."<test_log>"
ORA-01555: snapshot too old: rollback segment number 16 with name "_SYSSMU1_2968021$" too small
ORA-02063: preceding line from ORCL.G1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
or
ORA-01555 caused by SQL statement below (SQL ID: <sql_id>, Query Duration=0 sec, SCN: 0x074e.f6cf3994):
SELECT /*+ OPAQUE_TRANSFORM */ "ACCOUNT_NO","CURRENCY_CD","SHORT_NM","LONG_NM","CLIENT_NAME","CLIENT_CD" FROM "MV_MT" "MV_T_M"
Errors in file /<DIR>/<instance_name>_ora_3184.trc:
Errors in file /<DIR>/<instance_name>_ora_3184.trc:
ORA-01555: snapshot too old: rollback segment number 83 with name "???" too small
There are several solutions that can assist here, outside of the normal UNDO Tuning practices from the note above:
1. Increase the UNDO_RETENTION parameter beyond the threshold seen from the alert.log message for the ora-1555.
2. Perform a COMPLETE Refresh
- Refresh the MVIEW singularly when the database is not too busy, using the ATOMIC_REFRESH=FALSE option.
(This will perform a TRUNCATE on the MVIEW Container table, rather than a DELETE operation)
3. Reduce UNDO needed for a refresh group
- Refresh the larger mviews individually to alleviate the UNDO usage pressure, as per the Complete refresh above.
4. Bulk Updates
Avoid bulk updates to the master tables while a refresh is being performed on mviews mastered by those tables.
If a bulk update has started while a refresh is active, the refresh must retrieve the undo generated by the update in order to maintain transactional consistency.
This can significantly increase the work done by the refresh which in turn increases the time needed. It could also result in a failed refresh due to ora-1555.
