| Increase of SYSAUX Tablespace - Oracle 10gr2 [message #216346] |
Fri, 26 January 2007 13:56  |
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  |
 |
Mahesh Rajendran
Messages: 10707
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 |
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 |
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 #246303 is a reply to message #246225] |
Wed, 20 June 2007 10:15 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
