Undo_Retention or ORA-01555:snapshot too old

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter. If any overwritten undo information is required for consistent read in a current long-running query, the query could fail with ORA-01555 snapshot too old error message. The ORA-01555 is caused by Oracle read consistency mechanism.

Oracles does this by reading the before image of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear. ORA-01555: snapshot too old error occurs, rollback records needed by a reader for consistent read, are overwritten by other writers. The properties of the UNDO_RETENTION parameter are mentioned below:

Parameter type – Integer
Default value – 900
Range of values – 0 to 232 – 1
Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

Avoiding the ORA-01555: snapshot too old error

Do not run discrete transactions while sensitive queries or transactions are running.
Use a large optimal value on all rollback segments, to delay extent reuse.
Use a large database block size to maximize the number of slots in the rollback segment transaction tables.
If necessary, add extra rollback segments (undo logs) to make more transaction slots available.
Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN.