Metalink Note:175434.1 has some interesting information on the subject.
For example, it reminds you that if the SYSTEM tablespace is LOCAL, then you cannot create a tablespace with extent management DICTIONARY.
You can tell the database creation assistant (DBCA) to create a locally managed system tablespace, and actually it's the default setting for DBCA in 9.2
The 9.2 SQL reference manual has the following caveat: if your compatibility parameter is set to less than 9.0 then by default tablespaces will be created with extent management dictionary.
Also from the 9.2 SQL reference manual:
------
If you do not specify the extent_management_clause, then Oracle interprets the COMPATIBLE setting, the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management. If the COMPATIBLE initialization parameter is less than 9.0.0, then Oracle creates a dictionary managed tablespace. If COMPATIBLE = 9.0.0 or higher:
If you do not specify the DEFAULT storage_clause, then Oracle creates a locally managed autoallocated tablespace.
If you did specify the DEFAULT storage_clause:
If you specified the MINIMUM EXTENT clause, then Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
If you did not specify MINIMUM EXTENT clause, then Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
------
It's still not clear to me why, when you omit EXTENT MANAGEMENT LOCAL for the SYSTEM tablespace, it defaults to DICTIONARY in 9.2, when all other tablespaces default to LOCAL. The SQL Reference documentation doesn't seem to mention it.
> -----Original Message-----
> From: Morten Egan [mailto:[EMAIL PROTECTED]]
>
> It was only in 9.2 that it was possible to create the system
> tablespace
> as locally managed, and rumour says that in 10i system tbs
> defaults to
> locally managed.
>
> The reason it's not default in 9.2 is most likely because......:
> 1. Most likely they just didn't update the DBCA default
> scripts to
> use this feature when they went from 9i R1 to 9i R2 (read:
> the scripts
> was just copied from the 9i R1 source tree)
> 2. The feature is brand new, so if some problems turn up, most
> databases wont have it set, because they are created from the DBCA.
>
> Godlewski, Melissa wrote:
>
> > List,
> >
> > System tablespace for Oracle 9iR2 is defaulting to
> Dictionary Extent
> > Management. I thought all tablespaces defaulted to Local in
> this release.
> >
> >
> > Thu Mar 6 15:29:43 2003
> > create tablespace SYSTEM datafile
> > '/oradb/DEV/system/system01.dbf' size 201M reuse
> > default storage (initial 10K next 10K) EXTENT MANAGEMENT
> DICTIONARY
> > online
> > Thu Mar 6 15:29:57 2003
> > Completed: create tablespace SYSTEM datafile
> > '/oradb/DEV
> > Thu Mar 6 15:29:57 2003
> > create rollback segment SYSTEM tablespace SYSTEM
> > storage (initial 50K next 50K)
> > Completed: create rollback segment SYSTEM tablespace SYSTEM
