Re: Locally Managed Tablespace for System Tabespace ?

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

> >

> >

>

>