When data is changed in the oracle database, the information needed to undo the changes is recorded so that the change can be undone (Rolled back). The record is basically just the original information.
Information is recorded in rollback segments and the undo tablespaces.
This information is also used to provide read consistency which allows long running transactions to always obtain the data as it was at the time that it began.
The recommended method of managing Undo is system managed Undo (SMU)
Set the UNDO_MANAGEMENT initialisation parameter to AUTO
Create an Undo Tablespace
CREATE UNDO TABLESPACE
You must also set the UNDO_TABLESPACE initilization parameter
To size the undo tablespace, you need to know how long the data is kept for: UNDO_RETENTION parameter
as well as how much undo is generated per second.
Find the undo rate:
You can use the dynamic performance view v$UNDOSTAT
If you recieve the error: “Snapshot too old”, increasing the retention peroid will help.
Retention is set to 30 seconds by default which is nrmally long enough but you can set this higher if necessary.