SYSAUX tablespace AWR issue.

Hi,

Oracle version 11.1.0.7.0. SYSAUX tablespace is constantly growing. Here are the occupants which are taking up space.

OCCUPANT_NAME Space (M)
---------------------------------------------------------------- -----------
SM/AWR 4,031.00
SM/OPTSTAT 1,250.00

I was able to take care of SM/OPTSTAT but I am having issues with SM/AWR. It grows everyday but I see only 3 snapshots even though retention period for snapshots is 8. I don't understand why the tables are so big when there are few snapshots and they keep growing. How should I go about this problem. I am new to Oracle and come from a DB2 background. what am I missing.

select SNAP_ID,begin_interval_time,end_interval_time from WRM$_SNAPSHOT;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
------- ------------------------- -------------------------
23363 31-JUL-12 03.22.44.202 AM 31-JUL-12 03.54.58.684 AM
23365 01-AUG-12 03.23.33.122 AM 01-AUG-12 03.52.52.998 AM
23364 31-JUL-12 03.54.58.684 AM 01-AUG-12 03.23.33.122 AM


But select * from dba_hist_snapshot gives no rows.

no rows selected

Retention is 8 days.

SQL> select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL
----------- -------------------- -------------------- ------------------------------
3246448383 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

Here are the sizes of the tables and all these tables have rows with Snapshots dating back to last year but the dba_history_snapshot has no info and returns 0 rows.


WRH$_SEG_STAT SYSAUX 86
WRH$_LATCH SYSAUX 92
WRH$_SQLSTAT SYSAUX 104
WRH$_PARAMETER SYSAUX 112
WRH$_SYSSTAT SYSAUX 128
WRH$_EVENT_HISTOGRAM SYSAUX 312
WRI$_OPTSTAT_HISTGRM_HISTORY SYSAUX 343
WRH$_SYSMETRIC_HISTORY SYSAUX 539
WRH$_ACTIVE_SESSION_HISTORY SYSAUX 660

Thanks,

Kanas

Check your snapshot retention period - as a work around, purge undesired awr data.

Paul,

Thanks for the reply. The retention period is set to 8 days. But a select on dba_hist_snapshot view returns 0 rows. But it the WRH$ tables I see snap_id going back to last year. Do you want to delete using dbms_workload_repository.drop_snapshot_range procedure. Please let me know.

Thanks,

kanas

Please check if dbms_scheduler jobs are working AND please check if fgr$autopurge_job is enabled.