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

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3aaa96f2_at_news.iprimus.com.au...
>
> "Mike Moore" <hicamel_at_mail.home.com> wrote in message
> news:1Jrq6.13164$zV3.1205087_at_news1.frmt1.sfba.home.com...
> > According to "ORACLE8i DBA Handbook" on page 589,
> > "In the case of SYSTEM tablespace, you can declare the extent management
> > as 'local' in the 'create database' statement. If you create the SYSTEM
> > tablespace
> > as locally managed, all of the rollback segments must be created in a
> > locally
> > managed tablespace. However, any of the other tablespaces in the
 database
> > can have dictionary-managed extents. The use of local extent management
> > with a specification for local size can ensure that space within the
> > tablespace
> > is reused effectively."
> >
> > It would have been nice if they had shown an example of the 'create
> > database'
> > syntax for doing this but I imagine that can be easily found in an
 Oracle
> > SQL
> > book.
> >
> > Mike
> >
>
> Thanks for this Mike. I will have a play this afternoon, and see if I
 can't
> get it to work.

I did, and it doesn't.

Nuno has suggested a hack of sql.bsq will do the trick (it doesn't for me), and syntactically it's impossible to issue a bog-standard SQL command that pulls this particular trick.

Now, I'm not expert at hacking sql.bsq (and there's a good reason for that: it's not advised, and I suspect not supported). So maybe I just don't know how to do it.

But as soon as it involves sql.bsq, I'm out of here... and any normally sane DBA would be as well (and no offence to anyone who ever has!)

Regards
HJR
>
> Though quite why anyone would want to bother with this in real life is
> frankly beyond me. It's only going to end up with a bigger SYSTEM
> tablespace than otherwise, that never fragments (and since you're not
> supposed to create and drop stuff in there for yourself, fragmentation
> (beyond that which Oracle itself introduces) wouldn't have been a problem
 in
> the first place).
>
> I think the quote you included sums it up perfectly: local management
> 'ensures that space within the tablespace is REUSED effectively". Who
> re-uses space within SYSTEM?
>
> Regards
> HJR
>
>
>
> >
> >
>
>
Received on Tue Mar 13 2001 - 05:02:26 CST