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.
SQL> SHOW PARAMETER UNDO
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS_01
SQL>
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.
SQL> ALTER SYSTEM SET UNDO_RETENTION = 7200 /* two hours
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.