This content has been marked as final. Show 5 replies
please check this thread:
i saw it ,but i had done what it say;
reduce the retention,or interval
but i found that the used space doesn't reduce;
sorry,my poor english, i hope you can understand what i say;
thk you !
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.
select dbms_stats.get_stats_history_retention from dua GET_STATS_HISTORY_RETENTION --------------------------- 31 what does non-awr means? useless? 'alter table move mean' reduce the hWM?
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?