sysaux将满如何解决？

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;

/