ORA-01555 Snapshot Too Old / ORA-30036: unable to extend segment by string in undo tablespace

ORA-01555 Snapshot Too Old

The “ORA-01555 Snapshot Too Old” error usually comes in long-running queries.

In Oracle, previous changes of the data are retained in undo segments.

For example, a query started at 1:00 pm after lunch, and after working 7 hours, got snapshot too old at 8:00 pm.

Normally, after the query starts to work, it takes the unchanged state of the read data from undo.

But if there is not enough space in UNDO, the query fails by taking this error.

To avoid this error you should increase UNDO_RETENTION.

But even if you increase UNDO_RETENTION, this error can continue.

In such a case, undo tablespace is insufficient. So you need to increase the size of the undo tablespace.

If you specify UNDO_RETENTION with RETENTION GUARANTEE as follows, you will be guaranteed to keep your UNDO data for the duration you specify in UNDO_RETENTION.

Of course, there must be enough space in the undo tablespace and the auto extend property of the datafiles must be On.

ORA-30036: unable to extend segment by string in undo tablespace

If you create UNDO_RETENTION with RETENTION GUARANTEE, you can get “ORA-30036: unable to extend segment by string in undo tablespace” error if there is no space in undo tablaspace.

In such a case, you need to grow the undo tablespace size sufficiently or reduce UNDO_RETENTION.

You can do better undo management by taking advantage of the Undo Advisor. “Undo Advisor (OEM)”