Re: Locally Managed Tablespaces - any cons???

"Kenneth Koenraadt" <plovmand_at_hotmail.com> wrote in message news:3b755433.6118311_at_news.mobilixnet.dk...
>
> On Sat, 11 Aug 2001 07:37:05 +1000, "Howard J. Rogers"
> <howardjr_at_www.com> wrote:
>
> >
> >"Kenneth Koenraadt" <plovmand_at_hotmail.com> wrote in message
> >news:3b7197c5.7098447_at_news.mobilixnet.dk...
> >>
> >> On Tue, 7 Aug 2001 21:20:37 -0600, "Ron Gardiner"
> >> <rongardiner_at_powersurfr.com> wrote:
> >>
> >> >New for 8i (I believe) is locally managed tablespaces...
> >> >
> >> >From what I've read, sounds like there is no reason NOT to use them.
 Can
> >> >they be used for all types of TS?... rollback segs, temporary, how
 about
> >> >system itself?
> >> >
> >> >Any feedback would be appreciated!
> >> >
> >> >
> >> >
> >> Hi Ron,
> >>
> >> You cannot create a locally managed SYSTEM tablespace on database
> >> creation. But you can convert it to LM later with a supplied package.
> >>
> >> The SYSTEM tablespace and the tablespaces for rollback segments must
> >> have the same extent management type, so if you convert the SYSTEM
> >> Tablespace to LMT, rememer to convert your RBS tablespace(s) to LMT as
> >> well.
> >>
> >
> >This is just not true.
> >
> >Firstly, whilst you may be able to convert SYSTEM into a LMT, it's
 strongly
> >advised not to. You may have noticed that SYSTEM also has 50%
 PCTINCREASE,
> >which is generally bad, and is autoextensible, which is also usually
> >considered not terribly efficient... but the point is, SYSTEM breaks all
 the
> >rules, and is best left to do so. LMT/DMT is just another example.
> >
> >Secondly, you appear to suggest that if SYSTEM is DMT, you must have DMT
> >rollback segment tablespace. Absolutely not true. Provided there is one
> >non-system rollback segment created (and brought online), you can create
 a
> >LMT rollback tablespace, and create (and online) one LMT'd rollback
> >segment... after which, the original DMT'd rollback segment can be
 dropped,
> >and all others created without a problem. And all of that can be done
> >perfectly well with a SYSTEM tablespace that is merrily chugging a way as
> >dictionary-managed.
> >
> >Incidentally, the SYSTEM tablespace in 9i is still dictionary managed,
 and
> >there is still no provision for creating it Locally Managed... yet
 another
> >indication that locally managed SYSTEM tablespace is not advised, nor
> >necessary.
> >
> >Regards
> >HJR
> >
> >
> >
> >
> >> Regards,
> >> Kenneth Koenraadt
> >> Systems Consultant
> >> Oracle DBA
> >> plovmand@<no-spam>hotmail.com
> >
> >
>
> I have revisited the Oracle Documentation, and must admit I cannot
> prove my statement. The only restriction is that rollback segments
> cannot be created in a System managed tablespace.(AUTOALLOCATE).

Any number of rollback segments can be created in the SYSTEM tablespace (though it is not a good idea to do so, for obvious reasons).

Try 'create rollback segment blah tablespace system', followed by 'alter rollback segment blah online', and you'll find it works perfectly normally. You now have a non-SYSTEM rollback segment in the SYSTEM tablespace, and that is entirely acceptable.

I don't understand the reference to autoallocate, since that only applies to locally managed tablespace, and as we've just agreed, SYSTEM can only be created dictionary managed.

>
> Now, you tell ME :
>
> Exactly WHERE does the documentation recommend not to convert SYSTEM
> tablespace to LMT, thus advising you not to use the built-in package
> which does just that piece of work ??
>

As I said before, the fact that SYSTEM is still dictionary managed in 9i, still has 50% PCTINCREASE and still has a maxextents set of 121 suggests that Oracle considers the SYSTEM tablespace to be a law unto itself, and that tinkering around with it is both unnecessary and unwise, both from their point of view and yours.

You might also consider why locally managed tablespaces were introduced in the first place: to resolve (primarily) the performance implications that arise from contention for UET$ and FET$ when hundreds of segments decide to extend at the same time. How many segments within the SYSTEM tablespace are going to extend at the same time? And therefore, what's the point?

Regards
HJR
>
>
> Regards,
> Kenneth Koenraadt
> Systems Consultant
> Oracle DBA
> plovmand@<no-spam>hotmail.com
Received on Sat Aug 11 2001 - 17:45:49 CDT