Sysaux talbespace contain awr、advisor and optstat. Default, the content of awr will retain 7 days,and optstat will retain 31 days.
You can change the default values by doing this:
awr:
execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 4320);
Here 4320 is 3*24*60 ( Convert the days to minutes). For 3 days, it 4320 minutes.
You can see the configuration of awr with this querying statement
SQL> select * from DBA_HIST_WR_CONTROL;
optstat: -- the old statistics. It can be used for reload statistics when the new statistics have made a bad executive plan(metalink,281793.1).
exec dbms_stats.alter_stats_history_retention(15); --15 means 15 dyas;
You can see the days of optstat retained with this querying statement:
select dbms_stats.get_stats_history_retention from dual;
Sometimes, the contents of awr or advisor or opstat may occupy too much space of sysaux tablespace so that the sysaux tablespace grows too bigger.So,if this condition hppen,you must clean the awr or optstat.Now ,how to do these?
awr:
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);
low_snap_id : The low snapshot id of snapshots to drop.
high_snap_id : The high snapshot id of snapshots to drop.
--You can query the Dba_Hist_Snapshot to make sure that the command above have done.
also, you must make sure the parameter statistics_level has set to typical(not all).
after you do that ,If the sysaux tablespace grows continued,maybe you must
rebuild the tables of awr.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql
optstat:
SQL> exec dbms_stats.purge_stats(sysdate-10); --sysdate-10 means that saving the data the day after sysdate-10. Internally,It delete some tables which save the data of the statistics.
So,if there is no use of doing that,try this:
sql> alter table WRI$_OPTSTAT_TAB_HISTORY move;
sql> alter table WRI$_OPTSTAT_OPR move;
sql> alter table WRI$_OPTSTAT_IND_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
sql> alter table WRI$_OPTSTAT_AUX_HISTORY move;
Take careful of the index of these tables after doing that.
References Documents:
Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER [ID 329984.1]
Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging [ID 454678.1]
SYSAUX Tablespace Grows Heavily Due To AWR [ID 852028.1]
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/14730395/viewspace-672137/ ,如需转载,请注明出处,否则将追究法律责任。