locally managed systems tablespace

[sudarshan]

Hi everyone ,



I want to know how to create a locally managed "systems' tablespace or how to alter system tablespace to locally managed .

The sybex book /oracle manual says systems tablespace can be created as locally managed , but not giving any syntex .I tried editing 'sql.bsq' but gives error in database creation.

[julian]

Examples:

DATA:

CREATE TABLESPACE SUDARSHAN_DATA DATAFILE '/dev/vx/rdsk/datadg/disk21'
SIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DATAFILE '/dev/vx/rdsk/datadg/disk21' AUTOEXTEND ON NEXT 128M MAXSIZE 3000M;

TEMP:

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/vx/rdsk/datadg/disk26'
SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

[jmodic]

AFAIK SYSTEM tablespace can not be localy managed, at least not in 8i (I don't know about 9i, though).

Somewhere in the manuals it says that SYTEM TS can be created as LMT, but the AUTOALOCATE option must be used for this. However this is a documentation bug - SYSTEM has to be dictionary managed in 8i.

[julian]

Originally posted by jmodic
AFAIK SYSTEM tablespace can not be localy managed, at least not in 8i (I don't know about 9i, though).

Somewhere in the manuals it says that SYTEM TS can be created as LMT, but the AUTOALOCATE option must be used for this. However this is a documentation bug - SYSTEM has to be dictionary managed in 8i.

This is absolutely true.

You can modify SQL.BSQ to set SYSTEM as LM but then you will get an Oracle error (the 00600 one). This is in 8.1.5.

In 8.1.6, strangely enough, you will not get an error. But don't even dream of successing in creating a LM system tablespace :-)

In 8.1.7 you will get ORA-2165.

I think it is not possible even in 9i.

[sudarshan]

Thanks for guidance ...