Re: Is it possible to create a System tablespace with Local managed extent?

"andrew_webby at hotmail" <spam_at_no.thanks.com> wrote in message news:984063065.26505.0.nnrp-02.c30bdde2_at_news.demon.co.uk...
> On this note (sort of), I'm trying some experiments just now on a test
> machine with locally managed tablespaces.
>
> I've got them all - except system, obv. - set to either autoallocate or
> uniform extent as appropriate, but when I tried to create rollback
 segments,
> it said I couldn't create them in my rbs tablespace... I take it - given
> sizing of rbs and initial/next/optimum clauses in rbs segment creation -
> that rbs can't be in locally managed tablespace?
>

This is my favourite 'feature' in 8i!!!!

Read the error message carefully... it should be the one about 'unable to use system rollback segment for updates in non-system tablespace'.

The problem is that in order to create a rollback segment in a locally managed tablespace, an update has to take place to the bitmap at the start of that non-system tablespace to record the extent allocations to the segment you're trying to create. That's an *update* to the bitmap. Updates require rollback. If the update is taking place anywhere other than SYSTEM, then a non-system rollback segment must be used to house the rollback involved.

So it's chicken and egg, catch 22, or whatever other cliche takes your fancy: you must have one, non-system rollback segment created in a dictionary managed tablespace *before* you can create all your other non-system rollback segments in locally managed tablespace. Once you've created the first locally-managed rollback segment (and brought it online, of course), then all others can be created without hassle -and that first one in dictionary managed tablespace can even be dropped.

Incidentally, just to make a lovely story even warmer and fuzzier, that first rollback segment can actually be created in the SYSTEM tablespace... it's not the tablespace the thing is housed in that determines whether the segment is the system rollback segment or not.

Anyway: short answer is that locally managed tablespace makes a proper and suitable home for all rollback segments (bar the system one, of course), but that it takes a bit of a fiddle to make it happen.

Regards
HJR
> In the O8 chapter that explains the concepts of lmt's, this isn't
> mentioned - though my 'explanation' seems logical enough.
>
> (ps. I haven't posted this to all the ng's in case you're wondering..)
>
> "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3AA72736.B43C354A_at_exesolutions.com...
> > > Is it possible to create a System tablespace with Local managed
 extent?
> > > Is it better off to have the system tablespace to be dictionary
 managed?
> >
> > It is definitely possible. Go to http://otn.oracle.com and enter the
 search
> > criterion "LOCALLY MANAGED". You will find lots of good reference
 material
> > on the subject.
> >
> > Daniel A. Morgan
> >
>
>
Received on Thu Mar 08 2001 - 13:25:51 CST