Re: Locally Managed Tablespace for System Tabespace ?

Thanks Niall,

I guess the word *default* needs some interpretation.

The ODCA by *default* through it's templates has the EXTENT MANAGEMENT LOCAL clause specified. Therefore it creates a LMT for System by default.

A manual creation of a database through CREATE DATABASE by *default* creates a DMT for System. You must specify EXTENT MANAGEMENT LOCAL clause in order to create a LMT for System.

I guess it won't be an issue for much longer when Oracle waves DMTs bye bye.

Thanks again

Richard
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d32828d$0$227$ed9e5944_at_reading.news.pipex.net...
> Default script (obtained from the database creation assistant is)
> CREATE DATABASE ZIGGY
> MAXINSTANCES 1
> MAXLOGHISTORY 1
> MAXLOGFILES 5
> MAXLOGMEMBERS 3
> MAXDATAFILES 100
> DATAFILE 'C:\oracle\oradata\ZIGGY\system01.dbf' SIZE 250M REUSE AUTOEXTEND
> ON NEXT 10240K MAXSIZE UNLIMITED
> EXTENT MANAGEMENT LOCAL
> DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
> 'C:\oracle\oradata\ZIGGY\temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT
640K
> MAXSIZE UNLIMITED
> UNDO TABLESPACE "UNDOTBS1" DATAFILE

'C:\oracle\oradata\ZIGGY\undotbs01.dbf'
> SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
> CHARACTER SET WE8MSWIN1252
> NATIONAL CHARACTER SET AL16UTF16
> LOGFILE GROUP 1 ('C:\oracle\oradata\ZIGGY\redo01.log') SIZE 102400K,
> GROUP 2 ('C:\oracle\oradata\ZIGGY\redo02.log') SIZE 102400K,
> GROUP 3 ('C:\oracle\oradata\ZIGGY\redo03.log') SIZE 102400K;
>
> The dictionary managed script would be
> CREATE DATABASE SPIDERS
> MAXINSTANCES 1
> MAXLOGHISTORY 1
> MAXLOGFILES 5
> MAXLOGMEMBERS 3
> MAXDATAFILES 100
> DATAFILE 'C:\oracle\oradata\SPIDERS\system01.dbf' SIZE 250M REUSE
AUTOEXTEND
> ON NEXT 10240K MAXSIZE UNLIMITED
> DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
> 'C:\oracle\oradata\SPIDERS\temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT
> 640K MAXSIZE UNLIMITED
> UNDO TABLESPACE "UNDOTBS1" DATAFILE
> 'C:\oracle\oradata\SPIDERS\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON
NEXT
> 5120K MAXSIZE UNLIMITED
> CHARACTER SET WE8MSWIN1252
> NATIONAL CHARACTER SET AL16UTF16
> LOGFILE GROUP 1 ('C:\oracle\oradata\SPIDERS\redo01.log') SIZE 102400K,
> GROUP 2 ('C:\oracle\oradata\SPIDERS\redo02.log') SIZE 102400K,
> GROUP 3 ('C:\oracle\oradata\SPIDERS\redo03.log') SIZE 102400K;
>
> Incidentally the scripts now have sensible names (like createdb.sql etc)
> rather than dbrun1.sql etc.
>
> so basically the create statement now allows standard datafile
> specifications to be included.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "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 - 04:48:22 CDT