Re: Doing some housekeeping -- is this legit?

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<j7jc90thkp22d54p08j1q82krp6p1p8578_at_4ax.com>...
> On Fri, 30 Apr 2004 23:07:14 +1000, "Howard J. Rogers"
> <hjr_at_dizwell.com> wrote:
>
> >Ed Stevens wrote:
> >
> >> On Fri, 30 Apr 2004 07:20:46 +1000, "Howard J. Rogers"
> >> <hjr_at_dizwell.com> wrote:
> >>
> >>
> >>>Brian Peasland wrote:
> >>>
> >>>>>I always thought that the only thing that should be in the SYSTEM
> >>>>>tablespace is the objects owned by SYS. But it appears that,at least
> >>>>>in some cases, these particular segments were created during DB
> >>>>>creation, before one gets a chance to alter the defaults for these
> >>>>>users.
> >>>>>
> >>>>>SELECT comments
>
> >>>>>FROM ng_participants;
> >>>>
> >>>>
> >>>>
> >>>>COMMENT
> >>>>---------------------------------------------------------------
> >>>>The SYSTEM tablespace should only contain data dictionary
> >>>>objects.
> >>>>This means those tables owned by SYS.
> >>>>Oracle 10g improves this with the SYSAUX tablespace to store
> >>>>things like OUTLN tables, AQ$ tables, etc.
> >>>
> >>>That's all true... but before you encourage people with pre-10g
> >>>databases to start moving Advance Queuing tables, Stored Outline tables
> >>>(and even AUD$) etc. into non-SYSTEM tablespace, please get them to
> >>>check the Oracle support status for doing so first. Because as far as I
> >>>can remember, it is not supported to move any of these things, and
> >>>therefore you just have to live with them being in SYSTEM.
> >>>
> >>>AUD$ is a particularly tricky example, because although Oracle's
> >>>documentation actually recommends moving it, it is unsupported to
> >>>actually do so!
> >>>
> >>>Just watch out for support issues before 'house-cleaning' the lot
> >>>somewhere new, is all I'm saying.
> >>>
> >>>Regards
> >>>HJR
> >>
> >>
> >> Well, I'm not planning on moving anything just yet, if at all. I was
> >> really looking for some more serious issues, like obvous user tables,
> >> but was mildly surprised to find these segments in there. I just
> >> checked an 8.1 db I created a couple of weeks ago on a Solaris system.
> >> I created it using DBCA (or whatever it was called in 8.1) and the
> >> only thing I did post-creation was change the default tablespaces for
> >> all users (which were all created under DBCA). That DB has 48
> >> segments owned by either OUTLN or SYSTEM that are in the SYSTEM
> >> tablespace.
> >
> >It's been that way for ages and ages (well, OUTLN was only invented in
> >8i, but you get my point!).
> >
> >Put it this way, the only two users that absolutely have to exist
> >regardless of version from the word go in every Oracle database are SYS
> >and SYSTEM. Likewise, the only tablespace that positively has to exist,
> >regardless of version, is SYSTEM. It's therefore not a major surprise
> >(or drama, frankly) that SYSTEM (the user)'s tables end up being stored
> >inside SYSTEM (the tablespace) -and have done so since time immemorial.
> >
> >Regards
> >HJR
>
> But I would assume that it is still considered 'best practice' to
> alter SYSTEM's (the user) default TS as soon as you get the database
> on it's feet, to prevent the prevent the inadvertent introduction of
> other (mostly very non-essential) segments into the SYSTEM ts?

Since SYSTEM has some objects that are "strange" (ie, put together in a way that is not recommended for user objects), I'd say the best practice is to leave it alone and just be sure other stuff isn't put there. An application can wind up with lots of stuff in the SYSTEM ts, since that is where stuff that applies to the whole system goes (like the underlayment to DBA views). This tends to be a problem if you have an app that has a gigantic number of extents and uses dictionary tablespaces, but that winds up being the exception to deal with if necessary.

In the V7 time frame I saw a number of sites that would, not knowing any better, put user stuff in the system ts, and usually there were enough things wrong to justify creating a new db from scratch.

jg

--
@home.com is bogus.
Aldous Huxley was an optimist:
http://timesofindia.indiatimes.com/articleshow/msid-654822,curpg-2.cms