问题
sysaux已经60G了,并且sm/other组件占用了48G空间。
select occupant_name,schema_name,move_procedure from v$sysaux_occupants;
select occupant_name, space_usage_kbytes/1024/1024 from v$sysaux_occupants order by 2;
select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB
from dba_segments
where tablespace_name='SYSAUX'
group by owner,segment_name,segment_type order by 4 desc )
where rownum <10;
- WRM$表存储awr的元数据(workload repository metadata)
- WRH$表存储采样快照的历史数据(workload repository historical)
- WRI$表存储数据库建议功能相关的数据
- WRR$ 11g的workload capture及workload replay信息
排查
解决
select occupant_name, space_usage_kbytes/1024/1024 from v$sysaux_occupants order by 2;
Truncate表 :删除原来的段,再为表新建一个段。将原来的存储空间释放,再重新分配新的区。