1 ) One single rollback segment is definitely insufficient. As
transactions usually don't share extents, the fact you are using one
single rollback segment is responsible for the unbounded growth of your
rollback segment.
You should have 4 rollback segments per 16 users, with a minimum of 4,
and rounded up to a multiple of 4.
2 ) If you have a single large transaction you should use set
transaction rollback segment <rollback segment name>
3) You shouldn't use the OPTIMAL clause or you will get 'snapshot too
old' errors, the infamous ora-1555
4) Your analysis is faulty: maxextents is not the sum of wraps+extents
5) the situation you now have is the transaction is being rolled back
when hitting the error. As you are using the optimal clause the
rollback segments shrink, and you don't observe any space problem. But
you definitely do have a space problem.
Read up on rollback segments in the concepts manual, and make sure you
have at least 4 rollback segments and don't use the optimal clause.
Even Oracle admits it was not one of their brightest inventions.
--
Sybrand Bakker
Senior Oracle DBA
Received on Thu Apr 06 2006 - 05:12:07 CDT
