Re: interpreting tablespace settings

From: Andrew Mobbs <andrewm_at_chiark.greenend.org.uk>
Date: 10 Jun 2002 18:35:24 +0100 (BST)
Message-ID: <u7x*xJtqp@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 Mon Jun 10 2002 - 12:35:24 CDT