Re: Problem with system tablespace

On Aug 6, 4:54 am, Helma <bad_elef..._at_hotmail.com> wrote:
> > > Is it possible to convert loaclly managed system tablespace to
> > > dictionary managed.
>
> i believe i've read once that it's not possible to convert the system
> tablespace to a dictionary managed one.
>
> Anyway, you should prefere locally managed tablespaces , so you
> haven't got a problem.

Actually, there was no problem leaving SYSTEM as dictionary-managed.

Why anyone would go out of their way to convert SYSTEM to locallymanaged  beats me. Locally-managed tablespaces were designed to make extent allocation trivial and to prevent tablespaces from fragmenting when objects were being continually dropped and created.

How much segment creation and dropping do you expect to do in the SYSTEM tablespace? I hope the answer is 'practically none', therefore that's one reason for LMTs disposed of.

And whilst objects do grow in SYSTEM, they don't do so maniacally (especially not now we've got SYSAUX for things like Statspack and Log Miner). So extent allocation is a trivial matter, and that's another reason for not bothering with LMTs in SYSTEM.

In 10g, you get SYSTEM as LMT by default. Fair enough: if Oracle makes it so, you might as well accept it (at least on this occasion). But if you happen to have a dictionary-managed SYSTEM tablespace, don't worry about it, and certainly don't start expending all the time and effort needed to convert to LMT: it's pointless. Received on Sun Aug 05 2007 - 18:13:08 CDT