Re: Rollback Segments Question

"Howard J. Rogers" <howardjr20002_at_yahoo.com.au> writes:

> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0304090754.7eb4ee76_at_posting.google.com...
> > Tim X <timx_at_spamto.devnul.com> wrote in message
> news:<87of3hs8vj.fsf_at_tiger.rapttech.com.au>...
> >
> > I didn't try it myself yet, so it might not work. Try to create a
> > PUBLIC
> > or PRIVATE rollback segment in some DMT tablespace (not SYSTEM), bring
> > in online, and then try to create rollback segments in LMT tablespace.
> > ORA-1552 actually says that SYSTEM rollback segment is used for
> > operation
> > it can't be used for. The remaining question is if PUBLIC or PRIVATE
> > rollback segment can be used to create other rollback segments. If it
> > doesn't work, then you need SYSTEM rollback segment for this operation
> > and LMT can't be used for rollback segments. Here's the extract from
> > Administration Guide (9.2):
>
> The problem is simply that if all your tablespaces, bar SYSTEM, are locally
> managed, then the allocation of the extents making up your new, first,
> rollback segment will involve flipping some bits in the bitmap at the head
> of the non-SYSTEM tablespace being used to house it. And that's a piece of
> DML taking place in non-system tablespace. And you can't do DML in
> non-system tablespace unless there's a non-system rollback segment to house
> the undo it generates. Which means you're in a catch-22 situation: you want
> to create a non-system rollback segment, but creating it generates undo
> which has to be housed in a non-system rollback segment.
>
> The solution is to have a non-system tablespace in a dictionary managed
> tablespace (because creating a rollback segment in such a tablespace would
> mean the extent allocations in such tablespaces gets handled by updates to
> the UET$ and FET$ dictionary tables, and DML on dictionary tables is housed
> within the SYSTEM rollback segment, which is OK).
>
> *Any* dictionary managed tablespace would do. It doesn't have to be SYSTEM
> itself. And once that new rollback segment is operational, you have a
> non-SYSTEM rollback segment, and can therefore merrily create all other
> rollback segments within locally managed tablespace. As soon as *those*
> segments are operational, the original dictionary-managed one can be
> dispensed with.
>
> Oracle 9i Release 2 makes this a bit more problematic, because it introduces
> the idea of a SYSTEM tablespace which is itself locally managed... and if
> that's the case, then the 'temporary' rollback segment does indeed have to
> be created within the SYSTEM tablespace.
>
> Although, of course, in 9i Release 2, you would be using automatic undo
> instead of manual rollback segments in any case, and the issue therefore
> wouldn't arise.
>

Howard,

Thanks for a very clear explination - after reading what you wrote it all seems obvious and makes perfect sense.

This NG has to be one of my favorite resources. Hardly a day goes by where I don't learn something and yet I've only posted about 4 questions in the 18 months I've been following it (and received answers every time)!

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!