Home » RDBMS Server » Server Administration » Increase of SYSAUX Tablespace - Oracle 10gr2
Increase of SYSAUX Tablespace - Oracle 10gr2 [message #216346] Fri, 26 January 2007 13:56 Go to next message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
Hello all,

We are having Oracle 10gr2 db with the size of 50GB.

Day by Day the Sysaux tablespace getting increased quickly.

The major occupants of SYSAUX tablespace are

Server Manageability - Automatic Workload Repository - SYS USER
Server Manageability - Optimizer Statistics History - SYS USER
Enterprise Manager Repository - SYSMAN USER

Is there any options to do some settings that to delete the old history of the above mentioned parameters so that SYSAUX tablespace size to be stablized.

I am new to Oracle 10g.

Please explain.

DB Version : Oracle 10.2.0.1.0
OS : SUN SOLARIS 5.8

Thanks and Regards,
Prashanth
Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #216348 is a reply to message #216346] Fri, 26 January 2007 14:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The major occupants of SYSAUX tablespace are
What about the other occupants?
Did you query v$sysaux_occupants?
It could be your AWR generatings stats. Check the usage in v$sysaux_occupants
Did you try adjusting the retention?
And if the SYSAUX datafiles are defined as autoextend on, then you want to set it to OFF.

[Updated on: Fri, 26 January 2007 14:25]

Report message to a moderator

Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #216352 is a reply to message #216348] Fri, 26 January 2007 14:56 Go to previous messageGo to next message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
Hi,

Yeah,the AWR generating stats are more.

Herewith attaching spool file of the v$sysaux_occupants.

How to adjust the retention for this?

I changed the autoextend mode to OFF for sysaux tablespace datafiles?

Please advice.

Thanks and Regards,
Prashanth
Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #216362 is a reply to message #216352] Fri, 26 January 2007 15:24 Go to previous messageGo to next message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
Hi,

I deleted the old snapshots using following procedure.

SQL> select min(snap_id), max(snap_id) from DBA_HIST_SNAPSHOT;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1392 1581

SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1392, high_snap_id => 1511, dbid =
>1183007297);
3 END;
4 /

PL/SQL procedure successfully completed.

Now the SYSAUX tablespace sizes have been increased.

Source: http://www.dbapool.com/articles/061906.html

Thanks for your help.

Regards,
Prashanth
Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #216365 is a reply to message #216362] Fri, 26 January 2007 15:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are supposed to use MODIFY_SNAPSHOT_SETTINGS.
The same URL will discuss about it.
First, you may want to understand the implications of what you are about to do.
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#PFGRF02601
By setting the retention period shorter, oracle will just reuse the old space.
Meanwhile,
>>Now the SYSAUX tablespace sizes have been increased.
If you set autoextend to OFF on the datafile correctly, there is NO way it is going to increase. Maybe, internal usage will increase (and get re-used).
Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #246082 is a reply to message #216365] Tue, 19 June 2007 17:26 Go to previous messageGo to next message
tmathew
Messages: 18
Registered: May 2007
Junior Member
Mahesh,

We have also this datafile autoextend on.I've only about 40m free space on this datafile if i set autoextend off.Do i need to resize the file in order to avoid any surprises after i make it autoextend off? Smile

we use the defult retention period(7 days) and interval is an hour.

btw,we run 10.2.2 on linux


Thanks,

Mathew

Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #246225 is a reply to message #246082] Wed, 20 June 2007 06:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Sys* tablespaces are not supposed to grow wild (unless you place custom objects on these).
So autoextend off will not give you any surprise.
Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #246303 is a reply to message #246225] Wed, 20 June 2007 10:15 Go to previous messageGo to next message
tmathew
Messages: 18
Registered: May 2007
Junior Member
Thanks much Mahesh.

but when I did more research on this, I found there are more than one row in DBA_HIST_WR_CONTROL table.

select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL
3819065008 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
3623696221 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

looks to me one of the id is optional database id and other is my local db

is that right?



Another thing I would like to ask is that when i set autoextend off for sys* datafiles, will it throw any error or any other impacts when the tablespace size reach it's max size?

i found no custom obejcts on sysaux tablespace than the following sysobjects.

SELECT username FROM dba_users WHERE DEFAULT_tablespace='SYSAUX';
XDB
SYSMAN
WMSYS
DBSNMP
SI_INFORMTN_SCHEMA
EXFSYS
ANONYMOUS
ORDSYS
MDSYS
ORDPLUGINS



Thanks,

Mathew








Re: Increase of SYSAUX Tablespace - Oracle 10gr2 [message #246305 is a reply to message #246303] Wed, 20 June 2007 10:17 Go to previous message
tmathew
Messages: 18
Registered: May 2007
Junior Member
btw,we have a logical standby db also exist for this primary.


Thanks,

Mathew
Previous Topic: Oracle linux version ibm864 arabic support
Next Topic: auditing the version of the client
Goto Forum:
  


Current Time: Mon Dec 05 12:35:04 CST 2016

Total time taken to generate the page: 0.12015 seconds