Re: SYSAUX tablespace

On Sep 20, 9:51 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> Ravi via DBMonster.com wrote:
> > Hi,
>
> > The sysaux tablespace is growing pretty faster than other tables in my DB.
> > I'm using 10g RAC.
> > Obviously all db related and grid agents details get stored there. Is this is
> > the usual character of this tablespace.
>
> > Already this tablespace has allocated with 9 datafiles still its 91% occupied.
> > By increasing the space with adding datafiles also increasing the maintenace
> > part.
>
> > Is there any recommendations to handle this.
>
> > Thank You.
>
> Have a look at the V$SYSAUX_OCCUPANTS view. It will show you who is
> using what kind of space. Querying this view over time will give you an
> idea of where your space is growing. You can also query DBA_SEGMENTS too.
>
> You may or may not need this much space. It depends on who is hogging
> this space and how much space that really is.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -

Ravi, you did not specifiy the file size. I think Oracle estimates that the AWR snaptshots will take 200 - 300M for every 10 sessions to hold the default 7 days of snapshots taken by default at one hour intervals.

You can change both these intervals to reduce the amount of data retained.

Oracle should automatically begin overwriting snapshots if it runs short of free space in the SYSAUX tablespace.

See dbms_workload_repository.modify_snapshot_settings( retention => seconds, interval >= 60, dbid = x)

HTH -- Mark D Powell -- Received on Thu Sep 20 2007 - 13:13:14 CDT