这些WRH$_开头的表是AWR的基表,生成AWR报告需要,占用的空间取决于AWR保留的时间和细粒度(默认7天),少数情况下有无法自动清理的情况,这部分空间只能通过手工truncate分区或表的方式回收,步骤如下:
1、创建新的分区
如果WRH$_表无法自动创建分区,可以用如下SQL创建新的分区,用于保留最新的数据:alter session set "_swrf_test_action" = 72;
--手工创建一个快照
exec dbms_workload.create_snapshot()
2、truncate旧的分区
生成truncate分区的SQLselect 'alter table '||object_name||' truncate partition '||subobject_name||' update global indexes;'
from dba_objects where object_name like 'WRH$%' and object_type = 'TABLE PARTITION' and created
;
检查输出的SQL,并执行。
3、truncate未分区的表
通过如下SQL找出大表:select owner,table_name,dbms_xplan.format_number(num_rows) num_rows,object_type,partition_name,(select count(*) from dba_tab_partitions p where s.owner=p.table_owner and s.table_name=p.table_name) part from dba_tab_statistics s where owner in ('SYS','SYSTEM') and table_name like 'WRH$%' and num_rows>1e6 order by s.num_rows;
根据排序直接truncate相关表:truncate table WRH$_TABLESPACE_SPACE_USAGE update global indexes;
truncate table WRH$_EVENT_HISTOGRAM update global indexes;
truncate table WRH$_MUTEX_SLEEP update global indexes;
truncate table WRH$_ENQUEUE_STAT update global indexes;
truncate table WRH$_SYSMETRIC_SUMMARY update global indexes;
truncate table WRH$_BG_EVENT_SUMMARY update global indexes;
truncate table WRH$_SYSMETRIC_HISTORY update global indexes;
truncate table WRH$_SQL_BIND_METADATA update global indexes;
truncate table WRH$_SQL_PLAN update global indexes;
4、定期清理
默认会自动清理,也可以使用如下SQL定期手工清理select dbid,min(snap_id) from WRH$_SQL_PLAN group by dbid;
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(dbid,start_id,end_id);
5、减少保留的时长或细粒度exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(8);
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>8*60*24);