1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too smal

Discussion in 'SQL PL/SQL' started by malli_18arjun, Sep 30, 2010.

  1. malli_18arjun

    malli_18arjun Active Member

    Messages:
    36
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Hyderabad
    Hi

    I have been running a PL/SQL report for one year data. The report ran for almost 8 hrs and resulting no data and with following log error

    Error:

    endError Code-1555
    Error MessageORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too small

    What does exactly that error means? Any one can explain

    Thanks in Advance
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Re: ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too

    This is from elsewhere,

    The process which generated this error was in need for rollback/undo records to generate its consistent view. At some point in time however, the data needed by your session (of which the undo records were written by another process) were marked as being free (a commit was done by that other session), and some other process already wrote other rollback/undo information to it.

    If you're using manual UNDO Mode or use an Oracle version <9i, you can try to set a larger optimal size on the rollback segments. You can issue following select to determine the current optimal size and the highwater mark size (maximum size ever reached since database startup).

    Code (SQL):
    SELECT rb.segment_name, rs.optsize, rs.hwmsize
    FROM v$rollstat rs, dba_rollback_segs rb
    WHERE rs.usn = rb.segment_id
    To change the optimal size you can issue following example:

    Code (SQL):
    ALTER ROLLBACK segment rbs0 storage (optimal 100m);
    Pay attention that the size you specify for optimal will be constantly allocated inside the tablepace. If you are using automatic UNDO mode, you can try to increase the value of undo_retention.

    For example,
    Code (SQL):
    ALTER system SET undo_retention=10800;
    The value of undo_retention is the time in seconds the database tries to keep the undo blocks (not mark as being free) inside the undo tablespace.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too

    Read here:

    http://oratips-ddf.blogspot.com/2008/02/dreaded-ora-01555.html

    Also, it isn't a really good idea to set an optimal size for rollback segments as Oracle can, and will, resize them on the fly which can also cause an ORA-01555.
     
  4. malli_18arjun

    malli_18arjun Active Member

    Messages:
    36
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Hyderabad
    Re: ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too

    Hi All,

    Thank you very much for all your information...