Tuesday, March 24, 2009

Space Management In Sysaux

The SYSAUX tablespace can grow larger than expected very quickly if Automatic Workload Repository (AWR) is taking too much space

1-Check the occupants

Select * from v$sysaux_occupants ;

2-Check the interval and retention of snaphshot generation

select * from DBA_HIST_WR_CONTROL ;

3-You can modify the interval and snaphshot generation e.x
interval =60 minutes
retention=4 x 24 x 60 = 5760 minutes default is 7 days
EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=> 60, retention => 5760 );