RE: Oracle10g CREATE DATABASE creates system tablespace as dictionary managed by default.

From the 10gR2 DBA Administration manual
>>

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. The COMPATIBLE initialization parameter must be set to 9.2 or higher for this statement to be successful. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary-managed SYSTEM tablespace.
<<

You have to be explicit when you write your command. I like to specify defaults in my commands just to remind myself what I am getting. With upgrades it is time to recheck the manual to see if any of the defaults changed.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bort, Guillermo Sent: Thursday, October 16, 2008 9:38 AM To: oracle-l_at_freelists.org
Subject: Oracle10g CREATE DATABASE creates system tablespace as dictionary managed by default.

Hi All,

   This is more of a comment than a question. Due to somenetwork problems I was unable to use the DBCA, so I was stuck creating a database manually. This is a 10.2.0.3 installation, and I've done it a few times on 8i and 9i, but never before on a 10g. This is the script I used to create the database. The weird thing is that with this script, in which I DO NOT specify locally managed for either system nor sysaux, the RDBMS created the SYSAUX tablespace as LOCALLY MANAGED and the SYSTEM tablespace as DICTIONARY MANAGED. Has anyone ever come across something like this before? I thought that 10g created tablespaces as locally managed by default (and it actually does... except for system aparently)

SQL> create database orc

  logfile   group 1 ('/u01/app/oracle/oradata/orc/redo1.log') size 100M,
            group 2 ('/u01/app/oracle/oradata/orc/redo2.log') size 100M,
            group 3 ('/u01/app/oracle/oradata/orc/redo3.log') size 100M
  character set          WE8ISO8859P1

            size 500M
            autoextend off
  sysaux datafile '/u01/app/oracle/oradata/orc/sysaux.dbf' 
            size 100M
            autoextend off
  undo tablespace undo
            datafile '/u01/app/oracle/oradata/orc/undo.dbf'
            size 10M
  default temporary tablespace temp
            tempfile '/u01/app/oracle/oradata/orc/temp.dbf'
            size 10M;

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

$emca -config dbcontrol db -repos create

SQL> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         DICTIONARY
UNDO                           LOCAL
SYSAUX                         LOCAL
TEMP                           LOCAL

4 rows selected.

SQL> Regards

Guillermo Alan Bort
DBA / DBA Main Team

EDS, an HP company

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l