Sunday 26 October 2014

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

The cause of the ORA-01555 error is when your transaction request an older image from a segment that is overwritten with another info.

In Oracle 9i/10g/11g:
The UNDO Tablespace and the UNDO_RETENTION parameter, here is in second and determine the time that an image will be in the UNDO tablespace before that image will be overwritte by the modification that will be in various segment in the database.
You can do 1 of this 2 modification on your DB or the 2 that depends from your DB and the many applications:

1. Increase the setting of UNDO_RETENTION:
to see the value use: SQL> show parameter undo_retention;
to change:                SQL> alter system set undo_retention=(new-value)

2. Increase the tablespace UNDO that contain the ROLLBACK segments
alter tablespace UNDO add datafile '(name-of-the-new-datafile)' size (size-value)M;