Re: SYSTEM and SYSAUX Tablespace Opinions Requested

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 07 Sep 2007 17:50:29 -0700
Message-ID: <1189212617.179635@bubbleator.drizzle.com>

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.

-- 
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 Fri Sep 07 2007 - 19:50:29 CDT