适用范围

Oracle SYSAUX表空间清理

问题概述

从10g开始，oracle引进了SYSAUX表空间作为SYSTEM的辅助表空间，用来存放EM相关的内容，以及表统计信息、AWR快照、审计信息等数据。默认情况下，随着时间久了，SYSAUX表空间会变的越来越大。达到分配的最大配额时，使用了SYSAUX表空间的数据库功能可能会失败，或者功能异常。

解决方案

1、检查表空间使用情况 发现sysaux表空间使用空间已达44G

set lines 200
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
Group By Tablespace_Name)
On Tablespace_Name = Fs_Ts_Name
ORDER BY Sum_Free_m / Sum_m ;

TABLESPACE_NAME                     SUM_M      MAX_M FREE_BLK_CNT SUM_FREE_M PCT_FREE  PCT_USED
------------------------------ ---------- ---------- ------------ ---------- --------- ------------
SYSAUX                              44032       1857            4       3851   8.7459% 91.2541%
PERFDATA                          5475818       3968        11151 1231233.38  22.4849% 77.5151%
MWS_FILE                            98292       3968           13 24096.8125  24.5155% 75.4845%
WEBDATA                              8192          6         6013  2376.0625  29.0047% 70.9953%
USERS                                1024        441            2      441.5  43.1152% 56.8848%
SYSTEM                               2048    1019.25            1    1019.25  49.7681% 50.2319%
MWS_APP                              9216       3443            4   4860.375  52.7384% 47.2616%
STATDATA                         30016.75       3968           97 17626.4375  58.7220% 41.278%
RESDATA                              3072       2092          106  2105.1875  68.5282% 31.4718%
UNDOTBS2                            55347       1210         2116      42184  76.2173% 23.7827%
UNDOTBS1                            50917        888         1765 42225.0625  82.9292% 17.0708%

2、查看SYSAUX表空间内详细占存储空间的比重信息

col Item for a30
col Schema for a20    
col MoveProcedure for a60
set lines 200  
SELECT occupant_name"Item",
round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
schema_name "Schema",
move_procedure "MoveProcedure" 
FROM v$sysaux_occupants  
ORDER BY 2 Desc; 

Item                           Space Used (GB) Schema               MoveProcedure