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: interpreting tablespace settings

Re: interpreting tablespace settings

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 13 Jun 2002 10:04:06 +1000
Message-ID: <m2RN8.11066$Hj3.35695@newsfeeds.bigpond.com>


Hi Vladimir,

When you manually create a database with the 'create database...' statement, the DEFAULT behaviour is to create a DICTIONARY managed system tablespace (ie. default behaviour is as with previous releases which is Oracle's preferred way of introducing changes).

However there is now the optional clause 'EXTENT MANAGEMENT LOCAL' with gives you the option to create the system tablespace as locally managed (new to 9i release 2). If you pick this option, then all other tablespaces MUST be locally managed (which shouldn't really be a issue).

When using the DCA, it implicitly puts in this clause to make such created databases have a locally managed system tablespace (ie. Oracle is making the very strong hint hint, wink wink that this is the way to go).

You can easily read up on this by accessing the Oracle documentation (such as the SQL Reference).

Hope this clears things up for you.

Richard

"Vladimir Begun" <Vladimir.Begun_at_oracle.com> wrote in message news:3D07AC36.B5DB46F1_at_oracle.com...
> Hi there!
>
> Sorry for bothering you again with this issue, but let me try to
> clarify this:
>
> Oracle9i SQL Reference Release 2 (9.2)/CREATE DATABASE
>

http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920 /a96540/statements_55a.htm#2061235
>
> "
> EXTENT MANAGEMENT LOCAL
>
> Use this clause to create a locally managed SYSTEM tablespace.
> If you omit this clause, the SYSTEM tablespace will be dictionary
> managed.
> "
>
> So, you do not need to explicitly ask Oracle to make it DICTIONARY
> managed. Sorry for confusion, if any.
>
> Kind Regards,
> --
> Vladimir Begun
> The statements and opinions expressed here are my own and
> do not necessarily represent those of Oracle Corporation.
>
> Andrew Mobbs wrote:
> >
> > Howard J. Rogers <dba_at_hjrdba.com> wrote:
> > >
> > >
> > >I'll buy this (like you, I don't currently have space to create another
9iv2
> > >database, so I'll take it on trust).
> > >
> > >Pretty big "default", though, even so: once SYSTEM's been created,
you're
> > >stuck with it until you create a new database. And create it by hand,
as I
> > >don't see an option to create a dictionary managed SYSTEM tablespace
using
> > >DBCA.
> > >
> > >As it is, I rather thought the 'create database' command only allowed
the
> > >specification of a datafile for SYSTEM... the actual creation of the
SYSTEM
> > >*tablespace* is done by the first operative line of sql.bsq (as ever).
Mine
> > >reads:
> > >
> > >create tablespace SYSTEM datafile "D_DBFN"
> > > "D_DSTG" online
> > >
> > >...and I wouldn't know where to stick the 'extent management
dictionary' in
> > >that little lot and make it work (uh oh, I feel an experiment coming
on...).
> > >
> > >So I'll modify my claim somewhat: *practically* you can't create
> > >dictionary-managed tablespaces in 9iv2. Not without an awful lot of
> > >forethought and effort -which we agree (I think) isn't worth expending
in
> > >the first place!! ;-)
> >
> > Not even then:
> >
> > SQL> CREATE DATABASE reg02
> > 2 MAXINSTANCES 1
> > 3 MAXLOGHISTORY 1
> > 4 MAXLOGFILES 5
> > 5 MAXLOGMEMBERS 3
> > 6 MAXDATAFILES 100
> > 7 DATAFILE '/ora01/app/oracle/oradata/reg02/system01.dbf' SIZE 250M
REUSE
> > 8 EXTENT MANAGEMENT DICTIONARY
> > 9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
> > '/ora01/app/oracle/oradata/reg02/temp01.dbf' SIZE 40M REUSE
> > 10 UNDO TABLESPACE "UNDOTBS1" DATAFILE
> > '/ora01/app/oracle/oradata/reg02/undotbs01.dbf' SIZE 200M REUSE
> > 11 CHARACTER SET WE8ISO8859P1
> > 12 LOGFILE GROUP 1 ('/ora01/app/oracle/oradata/reg02/redo01.log') SIZE
102400K,
> > 13 GROUP 2 ('/ora01/app/oracle/oradata/reg02/redo02.log') SIZE
102400K,
> > 14 GROUP 3 ('/ora01/app/oracle/oradata/reg02/redo03.log') SIZE
102400K;
> > EXTENT MANAGEMENT DICTIONARY
> > *
> > ERROR at line 8:
> > ORA-25141: invalid EXTENT MANAGEMENT clause
> >
> > SQL>
> >
> > regattalp1(/ora01/app/oracle/admin/reg02/scripts) $ oerr ora 25141
> > 25141, 00000, "invalid EXTENT MANAGEMENT clause"
> > // *Cause: An invalid option appears for EXTENT MANAGEMENT clause
> > // *Action: Specify one of the valid options: UNIFORM SIZE,
AUTOALLOCATE
> >
> > Reading the Concepts guide, it says "If you created your database with
> > an earlier version of Oracle, then you could be using dictionary
> > managed tablespaces." (p 3-13)
> >
> > It appears that the only way to get Dictionary Managed Tablespaces in
> > 9iR2 is to have upgraded the instance.
Received on Wed Jun 12 2002 - 19:04:06 CDT

Original text of this message

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