During a quite evening of my last on-call bout I was alerted from our monitors that the UNDO tablespace was running out of free space. Thought of adding of a new data file and be done with it; When I checked the current allocation for this tablespace it was already at 40G - couldn't believe what I was seeing. The undo_retention was set to 7200 and max query length in v$undostat was not that high. One column that did caught my eye was the tuned_undoretention, its value was way very high. A little bit of investigation on tahiti and metalink confirmed my doubts - Oracle was not calculating the undo retention value correctly when we had configured the instance for automatic undo management. A quick search on metalink confirmed that this instance was suffering from 10g bug 5387030 (Automatic tuning of undo_retention causes unusual extra space allocation) This bug is supposed to be fixed in 10.2.0.4. If you are running an instance at 10.2.0.3 or below the only option would be to add the following to your instance parameter file. Good news is this parameter can be set via "alter system" command without the need for an instance bounce.


This causes the v$undostat.tuned_undoretention to be calculated as the maximum of: maxquerylen secs + 300 undo_retention specified in init.ora