Re: shrinking rbs?

Comments (and possibly more questions) embedded...

"Doug O'Leary" <dkoleary_at_mediaone.net> wrote in message news:MPG.1580b48fd2d7226c989793_at_news.randori.com...
> In article <thd2nb7jerf09a_at_beta-news.demon.nl>, postbus_at_sybrandb.demon.nl
> says...
>
>
> He is partly correct, but mostly incorrect.
> You are indeed risking incurring 1555, but not because they are in use,
 but
> because you make them too small. Selects are no transactions and do not
> extend the rollback segment.
> His resolution to alter the rollback segment offline and online, would,
> AFAIK, not change anything.
>
> I didn't think that sounded right; however, I'm not sure I understand
> your reason for the "snapshot too old" risk either. Let me outline what
> I believe our situation was and what I think you're saying and you can
> correct where I'm having the problem:
>
> 1. Our rollback segments started with two extents each and grew from
> there. Since optsize wasn't set, the rollback segments never shrunk back
> down and eventually reached their maxextents of 121 (thus, the ora-1628
> errors).

Guidelines suggest minextents of 20 for rollback segments. 2 seems very low. If it were me, I'd set optimal to be initial*20 (next_extent same as initial).

> 2. When I manually shrunk the rollback segments, they went back down
> to two extents each. Now, I take it, from what you and the our resident
> dba are telling me, that if there was an active transaction happening at
> that specific time and if someone else were running long winded queries
> against the database, then I'd be at risk for the snapshot too old
> errors. This is because there's a strong possibility that the rollback
> segment extents the transaction was using just got shrunk out of
> existance. Do I got that right?

If there was a transaction (delete, update, insert - not a select in other words) in that segment at the time that used extents you were trying to shrink, then Oracle *shouldn't* allow you to shrink it. Oracle goes a long way to ensure consistency in the system - and shrinking an active segment would ruin that.

> If that is right, I have a bone to pick with Oracle (gee, I'm sure
> they'll get right on that!). If you're shrinking rollback segments,
> wouldn't it make sense to *not* remove extents that are actively being
> used?

Yep, which is why (logically) Oracle shouldn't allow it. It's like trying to resize a datafile underneath the high-watermark. Can't do it... Like trying to de-allocate table extents under the HWM - can't do it etc...

> Assuming again that #2 is correct - is there a correct method to manually
> shrink rollback segments without risking the snapshot errors?

I think when Sybrand talks about 'snapshot too old' errors, it is because you have existing rollback segments that are too small for the job. This would occur in normal usage. This is a setup issue - not something you could run into because you attempted to shrink something.

This is my understanding of the situation anyway (am still learning myself). If I'm incorrect, I look forward to learning otherwise... ;-) Received on Fri Jun 01 2001 - 05:45:47 CDT