System Tablespace as Local Managed

[D Maverick]

Guys,

What's the corect syntax for creating locally managed SYSTEM tablespace using the CREATE DATABASE command?

Thanks.

[hrishy]

Hello

Sorry you cannot have a sytem tablespace as locally managed.

regards
Hrishy

[julian]

Originally posted by D Maverick
Guys,

What's the corect syntax for creating locally managed SYSTEM tablespace using the CREATE DATABASE command?

Thanks.

Not possible even in the near future (for many reasons).

[Mr.Hanky]

Ok, I'm curious. WHY on earth would you want to do that?
MH

[TimHall]

Because LMTs are so damn cool people just can't get enough of them

This is not possible in 9i either!

[D Maverick]

Actually i was also curious about it, i read it from -

Sybex OCP: Oracle8i DBA
Architecture & Administration and Backup & Recovery Study Guide by Doug Stuns & Biju Thomas

Page 153 (Managing Tablespaces)

"The SYSTEM tablespace can be locally managed; you must specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE command."

Thank you for the clarification, i guess the author of the book was drunk when he wrote that section.

[TimHall]

More like I was drunk!

It does appear to be a valid option in 9i during creation. You just can't migrate a dictionary managed system tablespace after creation.

I'm creating one now to test that it isn't a mistake. Sorry for throwing you off the scent!

Sorry :(

[TimHall]

It works! I now have a 9i instance with a Locally Managed System Tablespace.

Please form an orderly queue to kick me square in the nuts!

[marist89]

[sambavan]

Here is an extract fromMetalink

CREATING A LOCALLY MANAGED SYSTEM TABLESPACE
--------------------------------------------
 
The Oracle 8i Concepts guide page. 3-8, states that you can specify that 
the system tablespace be created as locally managed by including the extent 
management local clause in your create database script......It states as 
follows:

"For the SYSTEM tablespace, you can specify EXTENT MANAGEMENT LOCAL in the 
CREATE DATABASE command"

The truth however, is that neither Oracle version 8.1.5 OR 8.1.6 supports 
the concept of a locally managed system tablespace.

In Oracle 8.1.5, any attempt to add the EXTENT MANAGEMENT LOCAL clause to 
your create database command will result in a syntax error OR an 
ORA-600 [3810] error.  In Oracle 8.1.6, any attempt to do so, results in 
no error....But the clause is ignored. In 8.1.7 an ORA-2165 is returned when 
using the clause in the create database command.

Locally managed system tablespaces are simply not yet possible.

Sam