Oracle Purging statistics

sysaux_occupants

SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/

select dbms_stats.get_stats_history_retention from dual;
The default value is 31 days.


exec dbms_stats.alter_stats_history_retention(10);
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
select dbms_stats.get_stats_history_availability from dual;


select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc


select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/


view plaincopy to clipboardprint?

alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
alter table WRI$_OPTSTAT_IND_HISTORY shrink space;
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object


select 'alter table '||segment_name||' move tablespace SYSAUX;'
from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'


select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'


select di.index_name,di.index_type,di.status from dba_indexes di
where di.tablespace_name = 'SYSAUX'
and di.index_name like '%OPT%'
order by 1 asc
/


exec dbms_stats.alter_stats_history_retention(1);
elect dbms_stats.get_stats_history_retention from dual;