Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Locally Managed Tablespace for System Tabespace ?

Re: Locally Managed Tablespace for System Tabespace ?

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: 15 Jul 2002 10:34:27 -0700
Message-ID: <agv1330296v@drn.newsguy.com>


In article <UMwY8.35407$Hj3.106468_at_newsfeeds.bigpond.com>, "Richard says...
>
>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.

And aren't we all waiting for that so we don't have to deal with that old c**p any more!

Pete
>
>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
>> > >
>> > >
>> >
>> >
>>
>>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Mon Jul 15 2002 - 12:34:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US