Whenever a change happens to the any of the record by a transaction, a snapshot of the record before the changes were made copied to the UNDO segments. These undo records serves the transactions which started before making changes to the original record. Oracle overwrites these undo records when the corresponding original record is committed and there is not enough room for new undo records. If this kind of UNDO wipe out happen the transactions which are using these old snapshots from UNDO segments will die with ORA-01555 error.
- For oracle 8i and below increase the number of rollback segments and use bigger rollback segments.
- Increase the undo tablesapce size
- UNDO_RETENTION=[seconds] parameter: Oracle 10g provides this parameter and it keeps or assures the undo records in undo segment at least the duration mentioned in the parameter. Increase the value of the undo_retention parameter as required for the process.
- Retention Guarantee: You can enable the undo retention guarantee by creating a tablespace with “Retention Guarantee” clause. You can specify this clause with ALTER TABLESPACE statement. If retention guarantee is enabled the specified minimum undo retention is guaranteed. The database never overwrites the unexpired undo data even if other transactions fail due to lack of space in the UNDO tablespace.
- Automatic Undo Management: if the database is running on Oracle 9i and above implement Automatic Undo Management.