Re: Doing some housekeeping -- is this legit?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 30 Apr 2004 23:07:14 +1000
Message-ID: <40924f7b$0$32558$afc38c87@news.optusnet.com.au>

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 Received on Fri Apr 30 2004 - 08:07:14 CDT