Just looking for a gentle prod in the right direction.
The biggest usage in my sysaux (10.2.0.3) tablespace are the following:
Now I've adjusted my awr retention policy and its taken affect (off topic but how often is this done and does MMom do it?).
Now I haven't figured out how to reduce the OPSTAT usage...
Like I said just a gentle prod...
when you have this query, the SM/OPSTAT is the highest consumer?
SELECT occupant_name, schema_name, move_procedure, space_usage_kbytes
SELECT occupant_name AS name,
schema_name AS schema,
Decode (move_procedure_desc, '*** MOVE PROCEDURE NOT APPLICABLE ***','N',
'Y') AS Moveable,
space_usage_kbytes/1024 AS space_in_mb
ORDER BY 3
Which gives (note space is in mb):
SM/OTHER Server Manageability - Other N 10.625
SM/OPTSTAT Server Manageability - Optimi N 675.5
SM/ADVISOR Server Manageability - Adviso N 95.9375
SM/AWR Server Manageability - Autom N 201.125
If I'm not wrong.. the OPTSTAT is the data stats related to the AWR, in which you have already cleaned. And so the statistics data can most probably be deleted with no harm.
try to find segments under WRI$_OPTSTAT* which consume more bytes:
WHERE segment_name in (
select table_name from dba_tables where tablespace_name = 'SYSAUX'
) order by bytes desc;
there could probably a dbms_? package that could clean the OPTSTAT.
Thanks, if is awr data and the retention policy isn't clearing up then we have a problem. Also the DBMS_WORKLOAD_REPOSITORY package can only drop snapshots (which I've tried and does indeed free space under SM/AWR).
I've opened a SR on this
By default, MMON process takes snaps every one hour and retains for 7 days. You can see your current settings with,
Originally Posted by bazza
select snap_interval, retention
About snapshots not getting cleared, check if you have baseline defined on them. MMON does not purge baseline snapshots even after retention period. You then have to purge such snapshots using DBMS_WORKLOAD_REPOSITORY package.
Click Here to Expand Forum to Full Width