System Tablespace as Local Managed

Guys,

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

Thanks.

Hello

Sorry you cannot have a sytem tablespace as locally managed.

regards
Hrishy

Quote:

---

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).

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

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

This is not possible in 9i either!

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.

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 :(

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!

http://www.theunholytrinity.org/crac...muccawhore.gif

Here is an extract fromMetalink

Code:

---




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

Quote:

---

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

---

Hm, this is new to me. Are you sure about your results? I know you can specify EXTENT MANAGEMENT LOCAL, but only in the "default temporary tablespace" part of the CREATE DATABASE command. And of course, LMT then applies only to the default TEMP tablespace, not to SYSTEM tablespace itself.

Also, on "9i New Features for Administrators" ILT we were explicitely told that system TS can not be LMT. And the "9i Database Administration" manual explicitely states:

"The SYSTEM tablespace is always dictionary managed."
http://download-west.oracle.com/otnd...aces.htm#19132

So once again: are you sure you sucessfully created SYSTEM TS as LMT? Have you verified that it really *is* a LMT and not perhaps that some of the utilities/tools simply erroneously report it as being LMT, while in fact it is normal dictionary managed TS?

Well aren't I the arse! I replied rather prematurely.

I created the new instance using the Database Configuration Assistant (Win2000 & 9.0.1.1.0) which allowed the LOCAL option to be selected for the SYSTEM tablespace. I started the creation and no errors were produced when the system tablespace was created. So I replied and left for home.

I've now logged in from home and the rest of the instance creation has finished. Turns out the DBCA ended up making it as dictionary managed SYSTEM tablespace anyway!

I've saved the creation script and it doesn't put the EXTENT MANAGEMENT LOCAL for the SYSTEM tablespace, regardless of your selection. I've repeated the process and added the EXTENT MANAGEMENT LOCAL in myself.

The result is it definitely fails!

Should have stuck with my first answer :)

Sorry guys & girls!

Since SYSTEM Tablespace is where the Dictionary is, wouldn't SYSTEM tablespace BE Locally managed in the sense that the dictionary is local to it. I realize this is not the same concept of local management, but in essence wouldn't it be about the same?

I thought that the point of the Locally managed tablespace is so that the Tablespace does not have to refer back to the dictionary in the System tablespace to manage itself.

So...I guess what I want to know is, what is the benefit of making SYSTEM tablespace local?

Quote:

---

Originally posted by TimHall
Should have stuck with my first answer :)

---

Gotta hate that. You think you are wrong, but you are wrong about being wrong.http://www.contrabandent.com/pez//ups/dvx_rune/lol.gif

It's possible that, if it were possible, it might be more efficient using the bitmap rather than rows in a table???

As you say, the contention aspect isn't really an issue here.

Quote:

---

Originally posted by marist89

Quote:

---

Originally posted by TimHall
Should have stuck with my first answer :)

---

Gotta hate that. You think you are wrong, but you are wrong about being wrong.http://www.dbasupport.com/forums/ima.../2001/12/7.gif

---

:) Made me LOL !

This whole forum thing scares the sh*t out of me sometimes. It makes me so paranoid. I often go to answer a question, get paranoid about my answer. By the time I've double checked it 10 people have already typed the answer.

I know it's not a race but this is harder than working for a living sometimes :)

You know that you cannot always be perfect! :D Example "Me" :) . So I would suggest that you post your answer and if it were to be wrong some one like "Jurij" will get hold of it and correct you. But you also need to be ready with resources to backup your answers.

Jeff, sorry, I have misplaced my books marks to back up this posting with some smilies. :cool:

Sam

Tim found a undocumented feature . . .

Why would Oracle say not to have SYSTEM as a LMT?

hmm let us think . . . maybe just maybe Oracle like to have one tablespace under their complete control?

I don't know thinking out loud . . .

or maybe other Oracle tools are expecting the SYSTEM tablespace to be untouched by mere mortals like us?

hmmm . . .

or make up your own reason . . . I going try this LMT on SYSTEM to see what happens.

E. Yen
OCP 8, 8i, 9i

Thank you very much guys. i guess ill have to make my system tablespace as Dictionary Managed for now. ill be recreating our production database next week (bec of block corruption in the system tablespace) =(

Anyway, how can i prevent block corruption?

Another question (actually i have already posted it on another thread) - Will you use LMT for your RBS? and why?

Thanks =)

There were some pretty cool animations in this thread, especially the one from Jeff.
Would really like to learn how to create and post these.

this is a test for posting animation, I hope Jeff Hunter won't mind :-(