Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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.