Re: Rollback Segments Question

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

Regards
HJR Received on Wed Apr 09 2003 - 17:29:47 CDT