Automatic Undo Management Improvements in Release 2

Oracle Database 10g Release 2 has come up with feature improvements for undo management. Release 1 has the undo retention guarantee feature; in Release 2, you can enable and disable undo retention using Oracle Enterprise Manager.

When you enable the undo retention guarantee option, the database will never overwrite the unexpired undo data. This will cause the database to leave unexpired undo data even at the risk of failure of active DML operations. This option is disabled by default, so that the database can overwrite the unexpired undo data and prevent DML operations from failing due to lack of free space in undo tablespace.

You must exercise caution when enabling the undo retention guarantee feature in Oracle Database 10g Release 2. Use this option only when you need to use flashback query in a controlled database environment, where you are sure of database transaction volumes and processing times.

The Undo Advisor interface has also received some enhancements in Release 2. Undo Advisor calculates the required undo tablespace size as the maximum of the auto-tuned value of undo retention and the low threshold value.

In the OEM, under System Activity and Tablespace Usage, you can track undos occurring within the previous seven days, instead of just one hour as is the case in Release 1.