Undo segments and disk usage

I have had a couple problems with undo in the past I want to make sure I'm on the right track. I'm using 9i with AUM and my retention is set to 900. First of all, I have read that 900 is just a guideline and oracle will overrule it if it needs to. It doesn't really matter to me as we don't use flashback queries.

Secondly, we had a serious issue where the undo tablespace grew to over 12 gig and filled up a hard drives at some sites. From what I understand, oracle keeps 10 segments in the undo area and selects expired segments almost randomly for transactions. We have a weekly transaction that runs for about an hour and generates over a gig of undo. Since we have our maxsize set to unlimited, you could theoretically have all 10 segments grow to over a gig after a couple months or so. My solution to this is to split up and optimize the long running weekly transaction. Also, I am putting a 4 gig maxsize on the undo.

If my concept of undo segments is correct then one could calculate the maximum amount of hard drive space used by your undo tablespace by multiplying the largest-amount-of-undo-used-per-period * number-of- segments. Where the 'period' in the previous equation would be the larger of the following: undo_retention or longest running query. In my case it would be 1.2gig * 10 = 12 gig of disk needed.

Do you guys think this sounds reasonable? Received on Mon May 21 2007 - 14:22:32 CDT