-
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
Last edited by kanas; 08-01-2012 at 06:12 PM.
Reason: More Information
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|