ORA-01555: snapshot too old: rollback segment number with name "" too small

rollback == undo

When you perform a query, all results must be returned by Oracle consistent with the point in time that the query began. If other sessions change data between the time you begin your query and when you read that data, your session must obtain a consistent read of the data block using the data in the rollback segment that was generated when the other session changed the data. Since rollback segments are reused cyclically, and since Oracle can't guess what you might need to read in the future, there is no way for Oracle to protect or preserve all the rollback entries your query might need over its lifetime. ORA-01555 occurs when the rollback entries you need for your query have been overwritten through cyclical reuse, or rollback segment shrinkage.

Even when you use Automatic Undo Management, as the previous example shows, you can get this error, since the UNDO_RETENTION parameter is set too low. This happens even when there is plenty of free space in the undo tablespace. Your best bet is to raise the value of the UNDO_RETENTION parameter so the necessary undo data isn’t overwritten before your long transaction finishes. The only certain way to avoid the snapshot-too-old error is to enable guaranteed undo retention in your database.

increase the retention time period to solve this

The V$UNDOSTAT table provides an indicator for helping figure out the undo retention interval. Query the V$UNDOSTAT view as follows:

    SQL> SELECT MAX(maxquerylen) FROM v$undostat;
    MAX(MAXQUERYLEN)
    -------------------------------
         210
         

The MAXQUERYLEN column of the V$UNDOSTAT view tells you the length of the longest executed query (in seconds) during the past 24 hours. The time set in the UNDO_RETENTION parameter should be at least as long as the time indicated in the MAXQUERYLEN column.

This is assuming you have enough space in the undo tablespace. If you don’t set a value for the UNDO_RETENTION parameter (or if you set a value of 0), Oracle automatically tunes undo with 900 seconds as the minimum value.

By default, Oracle Database 10g automatically tunes the undo retention period. Oracle recommends that you not set a value for the UNDO_RETENTION parameter unless your system has Flashback or LOB retention requirements.

A high value for the UNDO_RETENTION parameter doesn't guarantee the retention of undo data for the duration specified by the UNDO_RETENTION parameter. You must use the RETENTION GUARANTEE clause to guarantee undo retention for a specified time.

*** IMPORTANT ***

Let's say you've configured guaranteed undo retention in your database by using the RETENTION GUARANTEE clause. If your undo tablespace is too small to accommodate all the active transactions that are using it, the following will happen: > Oracle will issue an automatic tablespace warning alert when the undo tablespace is 85 per- > cent full (if you haven't disabled the automatic tablespace alert feature). > Oracle will also issue an automatic tablespace critical alert when the undo tablespace is 97 percent full. > All DML statements will be disallowed and will receive an out-of-space error. > DDL statements will continue to be allowed.

The older versions of Oracle, you are most likely familiar with the SET TRANSACTION USER ROLLBACK SEGMENT . . . statement, which enabled you to assign large rollback segments to a transaction to avoid the snapshot-too-old error. You can use this statement only under manual undo management. If you're usingthe Oracle-recommended automatic undo management, the database will ignore this statement if you use it however, no errors are generated.