ORA-01555: snapshot too old: rollback segment number string with name “string” too small

Mehmet Salih Deveci

5 years ago

I will explain how to solve ORA-01555: snapshot too old: rollback segment number string with name “string” too small error.

ORA-01555: snapshot too old

Details of error are as follows.

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

Cause: rollback records needed by a reader for consistent read are overwritten by other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

ORA-01555: snapshot too old: rollback segment number string with name “string” too small

Undo_retention

To solve this problem, increase the undo_retention as follows.

SQL> show parameter undo

NAME              TYPE               VALUE
------------------------------------ ----------- 
undo_management   string             AUTO
undo_retention    integer            900
undo_tablespace

SQL> alter system set undo_retention=9000 scope=both sid='*';

System altered.

Undo Tablespace in Oracle

If you have more capacity on Undo Tablespace, you can set undo_retention more than 9000.

Or add datafile if there are no space on Undo Tablespace, then add datafile to Undo tablespace as follows.

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

Now problem has been solved.

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )