Re: interpreting tablespace settings

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.