Re: shrinking rbs?

"Doug O'Leary" <dkoleary_at_ro05-24-29-232-217.ce.mediaone.net> wrote in message news:ECtR6.157$Dd.96010533_at_news.randori.com...
> Hey;
>
> I'm trying to confirm/deny something our resident dba told me.
> A couple of days ago, I noticed that we were getting some ORA-1628
> messages on our rollback segments indicating they'd reached their
> max_extents. I looked into it and sure enough they had - and optsize
> wasn't set.
>
> I fixed the problem by manually shrinking all four rollback segments
> by:
>
> alter rollback segment r0# shrink;
>
> Our dba told me that I had risked getting a number of "snapshot too
> old" messages as some of those rollback segments could have been in
> use when I shrunk them. He told me the safer way to fix the issue
> is to alter the rollback segment offline then back online before
> shrinking.
>
> Granted, I'm *way* on the junior side of junior when it comes to
> Oracle dba work; however, I have been doing a bunch of reading
> over the past several months. I don't remember reading anything
> anywhere about that. I would think that good error checking on
> Oracle's part, oxymoron that that is, would prevent the situation
> that our dba was postulating.
>
> Is the dba correct? Should I have cycled the rollback segments
> before shrinking them? Fortunately, it's a moot point as there
> weren't any issues, but it'd be good to know for future reference.
>
> Thanks for your time.
>
> Doug O'Leary
>
>
> --
> ------------------------
> Douglas K. O'Leary
> Senior System Administrator
> dkoleary_at_mediaone.net

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.

Hth,
Sybrand Bakker, Oracle DBA Received on Thu May 31 2001 - 13:20:29 CDT