Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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...Received on Mon Jul 15 2002 - 03:06:37 CDT
> 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
> >
> >
>
>