Re: interpreting tablespace settings

Richard

There are two threads are running here about this -- have a look at another one ("Oracle Myths")... It was clear for me from the beginning -- belive me I've read the documentation :) I posted the reference (see the quotation below) to that just to clear things up. Concerning DBCA... it does not put it implicitly. DCA uses configuration templates, you can create your own by the way.

Thanks.

Regards,

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Richard Foote wrote:


> 

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