Re: Locally Managed Tablespace for System Tabespace ?

The whole create database command has got very complicated indeed in 9i. So, no... Dictionary managed tablespace is still the default and I shouldn't have said otherwise. Using the GUI assistant, it's the default, but that's a completely different kettle of fishbits.

What I love about Oracle documentation on this subject is statements like this one:

"If you specify this clause [ie, "extent management local"], the database must have a default temporary tablespace, because a locally managed SYSTEM tablespace cannot store temporary segments.   a.. If you specify EXTENT MANAGEMENT LOCAL but you do not specify the DATAFILE clause, you can omit the default_temp_tablespace clause. Oracle will create a default temporary tablespace called TEMP with one datafile of size 10M with autoextend disabled.
  b.. If you specify both EXTENT MANAGEMENT LOCAL and the DATAFILE clause, then you must also specify the default_temp_tablespace clause and explicitly specify a datafile for that tablespace. " If...then..elseif...else..whatever...but also....god knows...

I particularly enjoyed this sentence from the same documentation:

"Oracle recommends that the total initial space allocated for the SYSTEM tablespace be a minimum of 5 megabytes."

Ha!ha!ha!ha!ha!...Deep breathing...Ha!ha!ha!ha!ha!

And the last time I created a 5Mb system tablespace was...., er.... , 1958.

Around the time you were celebrating your 21st, Richard!!

Best Regards
HJR "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:2mbY8.34691$Hj3.104871_at_newsfeeds.bigpond.com...
> Hi Howard,
>
> Clarification please.
>
> Are you suggesting that a 'CREATE DATABASE ZIGGY;' creates a System LMT
> (default) in 9.2. If so, what is the option to create a System DMT ?
> Curious.
>
> PS. Hoping my 9.2 CDs will arrive in post tomorrow !
>
> Thanks
>
> Richard
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:agoqqi$clj$1_at_lust.ihug.co.nz...
> >
> > "Ora" <ora_geek_at_yahoo.com> wrote in message
> > news:fa5d6718.0207122046.86b4197_at_posting.google.com...
> > > Is it possible to have Locally Managed Tablespace (LMT) for SYSTEM
> > Tablespace also ?
> > >
> >
> > Not until 9i Release 2, when an LMT SYSTEM tablespace is in fact the
> > default. Until then, it was utterly impossible to create SYSTEM as
locally
> > managed, despite Oracle's own documentation (and one or two posters
here)
> > stating otherwise.
> >
> > > If so , how can we convert an existing Dictionary Managed SYSTEM
> > Tablespace to LMT ?
> >
> > You can't. I don't even think it's possible in 9i Release 2 (converting,
I
> > mean). I think you have to create it as locally managed in the first
place
> > (and remember, as I say, it's locally managed by default anyway).
> >
> > As it is, you really don't want to convert *any* tablespaces to LMT. The
> > conversion procedure supplied by Oracle doesn't do a particularly good
> job,
> > and it's much better wherever possible to create new LMT tablespaces
from
> > scratch and use the MOVE command to put your existing segments into it.
> When
> > the old dictionary stuff is empty, drop it and have done.
> >
> > Regards
> > HJR
> >
> >
> > >
> > > OrA
> >
> >
>
>
Received on Mon Jul 15 2002 - 07:27:47 CDT