Oracle automatic undo retention

Automatic Undo Retention
In Oracle9i, administrators had their choice of continuing to manage rollback segments on their own (manual undo management) or configuring the database to manage its own before image data (automatic undo management). Oracle refers to system managed before image segments as undo segments.

You didn't have to be an Oracle expert to know that manual rollback segments were "somewhat troublesome." Out of space conditions, contention, poor performance and the perennial favorite "snap shot too old" errors had been plaguing Oracle database administrators for over a decade. Oracle finally decided that the database could probably do a better job of managing before images of data than we could.

But implementing automatic undo retention didn't necessarily guarantee users a trouble free undo environment. There really is only one parameter that administrators can tweak in a system that uses automatic undo. The UNDO_RETENTION parameter specifies the amount of time in seconds that Oracle attempts to keep undo data available. Setting this parameter to the appropriate value could be described as more of an art than a science.

Set it too low and you are wasting disk space. In addition, you aren't taking advantage of being able to flashback your data to as far back as the disk space allocated to the undo tablespace allows. Set it too high and you are in danger of running out of freespace in the undo tablespace.

10G R2 comes to the rescue! The database now collects undo usage statistics, identifies the amount of disk space allocated to the undo tablespace and uses that information to tune the undo retention period to provide maximum undo data retention. Administrators can determine the current retention time period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view.