Re: interpreting tablespace settings

Hi Andrew,

EXTENT MANAGEMENT DICTIONARY is not a valid option in the CREATE DATABASE statement BUT it is the default behaviour. By NOT specifying EXTENT MANAGEMENT LOCAL (which is the valid clause), you create a database with a DICTIONARY managed system tablespace.

Hope this helps

Regards

Richard
"Andrew Mobbs" <andrewm_at_chiark.greenend.org.uk> wrote in message news:u7x*xJtqp_at_news.chiark.greenend.org.uk...
> 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.
>
> --
> Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Received on Wed Jun 12 2002 - 19:20:10 CDT