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;
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).
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?).
By default, MMON process takes snaps every one hour and retains for 7 days. You can see your current settings with,
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.