Re: SYSTEM and SYSAUX Tablespace Opinions Requested

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 09 Sep 2007 10:06:31 -0700
Message-ID: <1189357583.610941@bubbleator.drizzle.com>

Frank van Bortel wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> DA Morgan wrote:

>> Frank van Bortel wrote:
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA1
>>>
>>> Dereck L. Dietz wrote:
>>>> In the database I work on at work it appears that there are many user
>>>> accounts which have been created with the system privilege UNLIMITED
>>>> TABLESPACE.
>>> Those account probably have the RESOURCE role granted.
>>> In previous releases (it started with 8.0, iirc), RESOURCE had
>>> unlimited tablespace.
>>>
>>>> I brought this to the attention of my manager mentioning that no user
>>>> account should have any tablespace quota in those two tablespaces. 
>>>> He had me write it up and then forwarded it to the off-site DBA to
>>>> see what he thought of my suggestions.
>>> SYSTEM - OK, SYSAUX, why not? A TS just as any other.
>> Ouch. That hurt. SYSAUX is not just another tablespace. There are very
>> specific rules as to what can move into and out of SYSAUX.
>>
>> col occupant_name format a30
>> col schema_name format a30
>> col move_procedure format a50
>>
>> SELECT occupant_name, schema_name, move_procedure
>> FROM v_$sysaux_occupants
>> ORDER BY 1;
>>
>> It might not hurt ... but neither does locking down security and
>> putting schemas where they can do no harm.

>
> undoubtedly Oracle had a cunning plan while making up sysaux,
> so your query will work (hopefully in real life versions, too)...
> But even then - what does it show?
>
> The point was: should users have quota on sysaux. My
> response was why not, (in that respect) sysaux is an ordinary
> tablespace - au contraire to system, which will cause
> an instance to behave very strange indeed when filled up
> and an extend is needed.
>
> Any thoughts on granting quota to users on SYSAUX
> - --
> Regards,
> Frank van Bortel

According to my understanding of what Oracle has documented we are supposed to treat SYSAUX with the same "hands-off" we do SYSTEM.

Here's what the docs say:
"The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database and that you must maintain. Other functionality or features that previously used the SYSTEM tablespace can now use the SYSAUX tablespace, thus reducing the load on the SYSTEM tablespace."

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/create003.htm#BABFHHAG

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Sep 09 2007 - 12:06:31 CDT