Re: ORA-01650 Issues with rollback segments for batch jobs

sybrandb_at_yahoo.com a écrit :

Hello Sybrand.

> 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.

Absolutely.
20 rbs are online and approx. 40 user sessions are active at the same time.
Anyway, the OLTP part of the DB don't experience any issues with RBS. The rbs I'm talking about are rbs taken online when needed i.e. for scheduled batch jobs.
At the time of scheduling, no other transactions are issued.

> 2 ) If you have a single large transaction you should use set
> transaction rollback segment <rollback segment name>
As I pointed in my post this is what I do already.

> 3) You shouldn't use the OPTIMAL clause or you will get 'snapshot too
> old' errors, the infamous ora-1555

Even for small rbs dedicated to small transactions ? For big ones, there are no concurrent transactions : no tx is likely to access data being deleted.

> 4) Your analysis is faulty: maxextents is not the sum of wraps+extents
Point taken. I had seriously a doubt about it myself. Sure it was a screwing coincidence.

> 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.
Ok. I do have space problem.
But why is my rbs's HWM lesser than the free space in the tablespace ?

> 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.
Thanks for the advice but I read the doc already (In doubt, RTFM always :) and relevant Metalink articles as well. As I said, the rollback segments work nice for the small transactions issued by the application. My main concern is about those used by batch jobs.

I'm going to set the default size of my rollback to the expected size of the transaction (covering size according to the doc) and remove the OPTIMAL value. In this case, should I monitor carefully the extensions of the RBS and shrink them manually if needed ?

> Even Oracle admits it was not one of their brightest inventions.
I agree. A migration to 9i is planned, I wish I have more success with UNDO management.

Thanks for your valuable answer Sybrand. Have a nice day.

PS : any idea about why the figure for OPTIMAL are negative ?

-- 
Sebastien Louchart