This content has been marked as final. Show 5 replies
run awrinfo.sql to find what is growing too much and post result of
-(1a) SYSAUX usage - Schema breakdown (dba_segments)
-(1b) SYSAUX occupants space usage (v$sysaux_occupants)
how many days do you have the retention? please, post too:
select dbms_stats.get_stats_history_retention from dual;
*by default is 31 days.
You can purge some days with:
exec dbms_stats.purge_stats(sysdate-X); --x= number of days
To reclaim the space of part 4 of awinfo.txt ((4) Space usage by non-AWR components (> 500K))
you must use "alter table move" option.
i would reduce the retention:
exec dbms_stats.alter_stats_history_retention(15); --for example 15 days instead of 31
With this you save only 15 days of statistics.
Oracle gets a lot of statistics, but not all was used in AWR report. These statistics not used in AWR report is called non-awr, it will save at tables with their correspondent indexes, ....
what does non-awr means? useless?
yes, to reclaim space at sysaux
'alter table move mean' reduce the hWM?