sysaux是system的辅助表空间,主要存放表的统计信息,awr快照,审计信息等.随着时间增长,如何减少该表空间的使用率?
1.修改统计信息保存天数
参考方法:
查看那些 occupant空间占用情况:
select OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 from V$SYSAUX_OCCUPANTS;
SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
修改成保存7天:
exec dbms_stats.alter_stats_history_retention(7);
查询方式:
select dbms_stats.get_stats_history_retention from dual;
删除16天前的统计数据:
SQL> exec dbms_stats.purge_stats(sysdate-16);
查看当前有效的统计数据是到什么时间的:
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
没有释放空间是因为“purge_stats”用delete的方式删除数据,虽然数据没了,但是HWM还没降下来,查看OPTSTAT使用哪些表,然后降低其高水位即可。
SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE'
再结合如下sql判断哪个表大,然后就move哪个表
SQL> select a.table_name,a.num_rows from dba_tables a where a.tablespace_name='SYSAUX' and a.table_name like '%OPTSTAT%';
如WRI$_OPTSTAT_HISTGRM_HISTORY,move 该表:
(1) alter table WRI$_OPTSTAT_HISTGRM_HISTORY move [tablepsace tablespace_name];
(2) alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;
(如果要同时压缩表的索引:ALTER TABLE WRI$_OPTSTAT_HISTGRM_HISTORY SPACE CASCADE)
再用如下语句查出相关表的索引,因为move表,索引会失效,需要重建索引
SQL> select i.index_name,i.table_name,i.status,i.table_owner
from dba_indexes i,dba_objects s where i.table_name=s.object_name and s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
如索引I_WRI$_OPTSTAT_TAB_ST,重建该索引:
alter index indexname rebuild;
2.修改awr快照保存时间
参考方法:
查看当前awr快照保存天数: select * from dba_hist_wr_control;
修改快照保存7天:
BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 interval => 15,
4 retention => 7*24*60);
5 END;
6 /
注:只能修改成比基线天数更大。
查看基线保存天数:
select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
设置基线保存5天:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
window_size => 5);
END;
/
查看最大和最小快照id:
select min(snap_id),max(snap_id) from dba_hist_snapshot;
删除快照:
begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id =>10785,
high_snap_id =>10900,
dbid =>38709299);
end;
/