一、问题描述
SYSAUX表空间做为SYSTEM表空间的辅助表空间,主要存放EM相关的内容以及表统计信息,AWR快照,审计信息等,而如果SYSAUX表空间在默认条件下你如果不做任何配置,随着时间的推移,会膨胀的越来越大。经过几次的不断扩展增加SYSAUX表空间,目前已经24G以上了,所以是该考虑减肥的时候了。
二、sysaux表空间分析与处理
1.检查表空间使用情况,发现sysaux表空间使用空间已达24G
- SYS@orcl1 > set lines 200
- SYS@orcl1 > Select Tablespace_Name,
- Sum_m,
- Max_m,
- Count_Blocks Free_Blk_Cnt,
- Sum_Free_m,
- To_Char(100 * Sum_Free_m / Sum_m, '99.9999')|| '%' As Pct_Free,
- 100 - To_Char(100 * Sum_Free_m / Sum_m,'99.9999') || '%' As Pct_used
- From (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 As Sum_m
- From Dba_Data_Files
- Group By Tablespace_Name)
- Left Join
- (Select Tablespace_Name As Fs_Ts_Name,
- Max(Bytes) / 1024 / 1024 As Max_m,
- Count(Blocks) As Count_Blocks,
- Sum(Bytes / 1024 / 1024) As Sum_Free_m
- From Dba_Free_Space