Oracle Database Discussions

Hello everyone

I've got a little question about the SYSTEM tablespace. I was wondering if the SYSTEM tablespace can have automatic segment space management instead of manual. I would think yes cause I read in the documentation that automatic segment space management can be used for permanent locally managed tablespaces.

Here's the db creation script that I wrote

**************
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT PFILE=\conf\initcyber10g.ora;

REM Put logfiles, control files on different disks!
CREATE DATABASE cyber10g
DATAFILE '\data\cyberlab_sys'
SIZE 256M AUTOEXTEND ON NEXT 10M
EXTENT MANAGEMENT LOCAL
LOGFILE GROUP 1 ('\data\log11cyberlab.dbf' , '\data\log12cyberlab.dbf') SIZE 10M,
GROUP 2 ('\data\log21cyberlab.dbf' , '\data\log22cyberlab.dbf') SIZE 10M
SYSAUX DATAFILE '\data\cyberlab_sysaux' SIZE 256M AUTOEXTEND ON NEXT 10M
DEFAULT TEMPORARY TABLESPACE TEMPORARY_DATA
TEMPFILE '\data\cyberlab_temp'
SIZE 512M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM
UNDO TABLESPACE UNDO_DATA
DATAFILE '\data\cyberlab_undo'
SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
CHARACTER SET WE8ISO8859P1;

CREATE TABLESPACE USER_DATA
DATAFILE '\data\cyberlab_usr'
SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

REM set the UNDO tablespace after database creation
ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_DATA;

REM execute catalog.sql and catproc.sql
SET ECHO OFF
@D:\oracle10g\rdbms\admin\catalog.sql
@D:\oracle10g\rdbms\admin\catproc.sql
SET ECHO ON

EXIT;
***************

And here's my init.ora file

***************
#=========================================================
# CYBERLAB DATABASE PARAMETERS
#=========================================================
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDO_DATA
remote_login_passwordfile = shared
db_block_size = 8192
shared_pool_size = 104857600
db_block_buffers = 5000
hash_area_size = 8388608
sort_area_size = 1048576
parallel_threads_per_cpu = 4
optimizer_mode = choose
optimizer_features_enable = 8.1.5
db_file_multiblock_read_count = 8
query_rewrite_enabled = true
db_name=cyber10g
control_files = (\data\control1.ctl, \data\control2.ctl)
processes=200
#---------------------------------------------------------
# CYBERLAB ONLINE BACKUP
# Remove #-characters to enable online backups
#---------------------------------------------------------
#log_archive_dest = D:\cyberlabdb10g\archive
#log_archive_format = "CYBER10G_%S_%T.ARC"
#log_archive_start = TRUE
#---------------------------------------------------------
*****************************


Now all my tablespaces are locally managed like I wanted. But my SYSTEM tablespace is has manual space segment management instead of automatic. The rest of the tablespaces are just fine. When I add 'SEGMENT SPACE MANAGEMENT AUTO' in the CREATE statement I get an error.

Can someone help me out on this one please? Is it best to leave it 'manual' for the SYSTEM tablespace? I ask this because the examples that can be found in the documentation always create the SYSTEM tablespace with manual segment space management and not automatic. The SYSAUX has automatic space segment though.

Any help would be greatly appreciated. Thanks in advance.

Christophe Bonte