Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
ORA-1555

  • Data-Articles
    • Desa Nelayan Makmur (166) Fri02,20:43pm

      KALAUツdilihat Desa Bendar tidaklah berbeda dengan desa-desa lainnya. Berada di tepi sungai Juwana, kecamatan Juwana, kabupaten Pati, Jawa Tengah. Pun, tempat ini memiliki pelelangan ikan pula. Yang membedakan dengan desa-desa nelayan lainnya adalah rumah-rumah kokoh dan besar. Ya, desa nelayan ini bukanlah sembarangan desa nelayan. Kebanyakan warga desa ini adalah pengusaha kapal penangkap ikan.…

      Read More...

ORA-1555

You've probably encountered the errorツORA-01555: snapshot too oldツduring the administration of your Oracle Database.

ツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツツ

Time 1 Session #1 starts a query on table A
Time 2 Session #2 updates row X in table A
Time 3 The query from session #1 arrives at row X andツ discovers the last updated time (based onツ the SCN number) of the row to be later than Time 1. So the UNDO or LOBツ segments (depending on the segment type) are used to get the read consistent view (before image) of theツ rowツ and then the query proceeds
Time 4 Session #2 updates row Y in table A and thenツ commits (thus making it possibleツ for this transaction slot to be overwritten)
Time 5 Session #2 updates row Z in table B andツ commits the record. Due to sub optimal configuration, mostly lack of space, the read consistent view for theツ updateツ of row Y in table A at time 4 is overwritten (we won't examine why theツ overwriteツ occurs at this point)
Time 6 The query from session #1 arrives at row Y and discovers the last updated time (basedツ on the SCN number) of the row to be later than Time 1. So the UNDO orツ LOB segments are examined to find the read consistent view. BUT theツ transaction slot containing the view was overwritten at time 5 so noツ readツ consistent view is available and an ORA-1555 occurs.

NOTE: UNDO is a generic term that can refer to either System managed UNDO (or Automatic Undo Management AUM) or Rollback segments

In which files is the error recorded?

The error is usually listed in one of the following files.

  • Alert Log file
    Messages such as the example below are generated:ツ
    ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small
  • Trace File generated at the time of the issue
    By default, no trace file is being generated automatically for ORA-1555, unless the 1555 event is set during the error occurrence
    alter system set events '1555 trace name errorstack level 3';
    In this case, the trace file name is reported in the alert log file whenever an 1555 error occurs - for example:
    Wed Jul 22 09:20:11 2015
    Errors in file /u01/sq/diag/rdbms/DB1/ DB1/trace/ DB1_ora_35236.trc:
    ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_417799848$" too small

Why does the error occur?

The root cause of this issue is mainly due to an UNDO block that has been overwritten and the query cannot build a copy of the data

at the time the query started. The common known issues are listed below:

  • The UNDO record for the row has expired.

    This means that the current time minus the commit time of the row is greater than theツUNDO_RETENTION. Once an UNDO recordツ
    of a committed row is 'expired', it is available for reuse.
    You may ask the question
    Why do my queries sometimes run so much longer than my UNDO_RETENTION before encountering an ORA-1555 and other times it occurs soon after?
    There is no definite answer and it all depends on the amount of activity and how busy the UNDO tablespace is.

    NOTE:ツThe UNDO records for an active or uncommitted transactions are marked as 'ACTIVE'. Once the transaction commits, the respectiveツ
    UNDO records are marked as 'UNEXPIRED' ie, it's been retained for the duration mentioned byツUNDO_RETENTIONツparameter (orツTUNED_UNDORETENTIONツ
    calculated by the system, incase of AUM). After retaining the UNDO records for the retention time specified, they are marked as 'EXPIRED' and are available for reuse.

  • The UNDO record for the row has NOT expired and yet was still overwritten.
    This scenario occurs when the UNDO tablespace has become full and the 'UNEXPIRED' UNDO records are being overwritten. This happens if the
    RETENTION GUARANTEEツis not enabled for the UNDO tablespace.
  • The LOB segment read consistent copy of the LOB segment is no longer available
    This depends on how the LOB column is configured, in-row or out-of-row. In-row LOBs should be utilizing normal UNDO algorithms in the UNDO tablespace.
    For Out-of-row LOBs use the information below.

    Read consistency with LOBs is controlled by 2 methods
    • Use ofツPCTVERSIONツ(old method)
      Specify the maximum percentage of overall LOB storage space used for maintaining the old version of the LOB. The default value is 10,ツ
      meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
      The downfall to this method is that through frequent updates or deletes in tables with LOBs it often exceeds even 100%ツPCTVERSION
    • Use ofツRETENTIONツ(current method for use with AUM)
      Use this clause to indicate that Oracle Database should retain old versions of this LOB column.
      Oracle Database uses the value of theツUNDO_RETENTIONツinitialization parameter to determine the amount of committed UNDO data to retain in the database.
      This method uses the same expiration as UNDO segments. If an ORA-1555 error occurs with a LOB segment, then this means that either:
      • PCTVERSIONツwas exceeded and the read consistent view of the LOB was overwritten
        or
      • the LOB has exceededツRETENTIONツand the row LOB was overwritten sometime during the query that generated the ORA-1555

Troubleshooting

Below is a list of steps to troubleshoot the issue:

1. Check Error Messages
Check alert log (or the logfile that contains the error) to determine the details of the error message as there are different types of ORA-1555 error messages:

ツツツツ a. Identifyツ segment name
ツツツツツツツツ ORA-01555: snapshot too old: rollback segment number with name "" too small
ツツツツツツツツ --> notice that segment name is null ""
ツツツツツツツツ or/and
ツツツツツツツ ORA-22924: snapshot too old
ツツツツツツツツ In this case 1555 error is reported while accessing UNDO data stored inside LOB segment. ORA-01555 for LOB segment is caused by one of the following reasons :

ツツツツツツツツツツツツ 1. LOB segment corruption:
ツツツツツツツツツツツツツツツツ To check the LOB table for corruption, review the following document :

ツツツツツツツツツツツツツツツツツツツツツツツDocument 452341.1ツORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lobツ Corruption

ツツツツツツツツツツツツ 2. If no LOB corruption is found, check for issues with Retention/Pctversion values :
ツツツツツツツツツツツツツツツツ You may need to increase Retention/Pctversion. Review the following document

ツツツツツツツツツツツツツツツツツツツツツツツツDocument 846079.1ツLOBs and ORA-01555 troubleshooting

ツツツツツツツツツツツツツツツ ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

ツツツツツツツツツツツツツツ -> notice that in this case the segment name exists "_SYSSMU107_1253191395$" which means UNDO data inside UNDO tablespace.

ツツツツツツツツツツツツツツツツ In this case, ORA-1555 error is reported while accessing UNDO data at UNDO tablespace which will discuss how to troubleshoot in following steps

ツツツツツツ b. Identify Query Duration
ツツツツツツツツツツ Failed query duration is logged in some ORA-1555 error messages in the alert log file or an application log.
ツツツツツツツツツツ ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):
ツツツツツツツツツツ If the query duration = 0 or few seconds, check the following document

ツツツツツツツツツツツツツツツツツDocument 1131474.1ツORA-01555 When Max Query Length Isツ Less Than Undo Retention, small or 0 Seconds

ツツツツツツツツツツ If the query duration is higher than theツUNDO_RETENTIONparameter set, increase theツUNDO_RETENTIONvalue to meet the query duration.

ツツツツツツツツツツ Ensure to increase the UNDO tablespace size accordingly. If the query duration is equal to or closer toUNDO_RETENTIONvalue, proceed with the following analysis.

2. Check Undo Datafilesツ
ツツツ select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensible
ツツツ from dba_data_files
ツツツ where tablespace_name in
ツツツ (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )
ツツツ group by file_id, tablespace_name, autoextensible
ツツツ order by tablespace_name;

ツツツ If you are using non-autoextensible UNDO data files, this can lead to high calculation ofツTUNED_UNDORETENTIONツand hence high allocation

ツツツ of UNDO space especially with large UNDO data files.
ツツツ To avoid this make sure that the UNDO data files are autoextensible (withツMAXSIZEツspecified) even if you have enough free space.

ツツツツNOTE:ツIt is strongly recommended NOT to have both autoextensible and non-autoextensible UNDO data files in the UNDO tablespace as this will

ツツツツツツツツツツツツツツツツ be lead toツTUNED_UNDORETENTION mis-calculation

3. Check TUNED_UNDORETENTION
ツツツツ SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
ツツツツ SQL> select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;
ツツツツツツツツツツツ
a. TUNED_UNDORETENTIONツis less than theツMAXQUERYLENツvalue:

ツツツツツツツツツツツツツツ This indicates there is a space pressure in the UNDO tablespace and hence the UNDO records are not retained for enough time period.

ツツツツツツツツツツツツツツ Adding space to the UNDO tablespace helps to resolve these cases.

ツツツツツツツツツツツ b. TUNED_UNDORETENTIONツis too high compared to theツMAXQUERYLENツvalue:
ツツツツツツツツツツツツツツツ This generally happens in case of UNDO tablespace with non-autoextensible datafiles. The internal algorithm tries to retain the UNDO

ツツツツツツツツツツツツツツツツ records as long as possible in such cases and hence high value ofツTUNED_RETENTION. The workaround will be to set all UNDO

ツツツツツツツツツツツツツツツツ datafiles to autextensible mode. (You can set aツMAXSIZEツfor the datafiles)
ツツツツツツツツツツツツツツツ Long running queries can raiseツTUNED_UNDORETENTIONツto very high values.
ツツツツツツツツツツツツツツツ You need to tune these queries to avoid retain UNDO data for more time inside UNDO tablespace. To identify these long queries, run the following :
ツツツツツツツツツツツツツツツ select maxquerysqlid, maxquerylen from dba_hist_undostat order by maxquerylen desc;
ツツツツツツツツツツツツツツツ select maxqueryid, maxquerylen from v$undostat order by maxquerylen desc;
ツツツツツツツツツツツツツツツツツツツツツツツツツ

4. High utilization of ACTIVE/UNEXPIRED extents
ツツツ select distinct status,tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name;
ツツツ Excessive allocation of ACTIVE/UNEXPIRED extents can be caused by one of the following reasons:For more details refer toツツツツツツツツツツツツツツツツツツツ

ツツツツツツツツツツツツ a. Large value of UNDO_RETENTION or TUNED_UNDORETENTION as described above.

ツツツツツツツツツツツツ b. Large UNDO data generation at certain point of time which can be identified by run following query :

ツツツツツツツツツツツツツツツツ alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

ツツツツツツツツツツツツツツツツ select begin_time , undoblks from dba_hist_undostat order by undoblks desc ;

ツツツツツツツツツツツツツツツツ select begin_time , undoblks from v$undostat order by undoblks desc;

ツツツツツツツツツツツツ c. Large dead transaction(s) rollback

ツツツツツツツツツツツツ d. Using flashback data archive

ツツツツ For more details refer to

ツツツツツツツツツツツツツツツツツDocument 1337335.1ツHow To Check the Usage of Active Undoツ Segments in AUM

5. UNDO_RETENTION :

ツツツ We recommend to setツUNDO_RETENTIONツto at least the average ofツMAXQUERYLENツand increase it if the ORA-1555 error is still reported.
ツツツ select avg(maxquerylen) from dba_hist_undostat;
ツツツ select avg(maxquerylen) from v$undostat;

            1. ツツツツツツツツツツツツツツツツツツツツツツ

Additional Info:

For further information, please refer to

or ask questions in the Database Administration community.

Add comment


Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email