ORA-01555: snapshot too old: rollback segment ...
- Product: Aleph
- Product Version: 20, 21, 22, 23
- Relevant for Installation Type: Dedicated-Direct,Direct,Local,Total Care
Description
A batch job stops with the following error -- or the util a/19 export log shows this error --:
Oracle error: fetch znnn
ORA-01555: snapshot too old: rollback segment number n with name "_SYS
(where "znnn" is a particular Oracle table, such as z01, z30, z0102, z13, z00p, z00r, etc.)
Resolution:
The ORA-1555 message indicates "rollback records needed by a reader for consistent read are overwritten by other writers". If you are running this job at a time when the online system or ue_01 is updating the file being read, then try running the job at a time when the pc_server and/or ue_01 are stopped.
If that doesn't prevent the error, you need to:
1. Do util o 13/1 to see how many datafiles there are for the UNDOTS tablespace.
2. Add another datafile to the UNDOTS tablespace, using util o/13/3.
3. Increase the undo_retention to 10800 (in $ORACLE_CONF/initalephn.ora). You may find that there is no undo_retention parameter. If so, add it:
undo_retention=10800
4. Stop/start ALEPH and Oracle.
If this error occurs on an SQL "select" of your own (rather than an Aleph batch service), we suggest adding the /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ immediately after the "select".
Note: the z00p is a *huge* table. See the article " Size of z00p table " in this regard.