| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: System tablespace Oracle 9202
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 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:meg_at_miracleas.dk]
>
> 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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Mar 06 2003 - 17:10:19 CST
 |
 |