SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等，而如果SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大。

经过几次的不断扩展增加SYSAUX表空间，目前已经20G了，所以现是考虑减肥的时候了。

?

1. 查看表空间使用

SQL>

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 AsSum_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 ByTablespace_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_MPCT_FREE PCT_USED

---------------------------------------- ---------- ------------ ---------- --------- ------------

SYSAUX 21652 1031 13 1032.5 4.7686% 95.2314%

NTICKET_DB 235520 7798 4922 46894 19.9108% 80.0892%

NTICKET_INDEX 112640 2418 764 25382 22.5337% 77.4663%

SYSTEM 2798 1884 7 1916.125 68.4820% 31.518%

USERS 5 3.6875 1 3.6875 73.7500% 26.25%

PORTALMAIL 200 168.125 2 170 85.0000% 15%

UNDOTBS2 10265 3596 200 9936.8125 96.8028% 3.1972%

UNDOTBS3 10265 3593 248 9983 97.2528% 2.7472%

UNDOTBS1 24455 3968 184 24280.625 99.2870% .713%

STRATEGY 20480 3968 6 20478 99.9902% .0098%

?

?

2. 查看SYSAUX表空间内各个分类项目占存储空间的比重，可以看到AWR快照占用了近20G左右的空间，统计信息为300M左右，同时数据库关闭了审计audit_trail，所以审计表aud$不占空间

?

SQL> col Item For a30

SQL> col "Space Used(GB)" For a10

SQL> col Schema For a20

SQL> col "MoveProcedure" For a200

SQL>

SQL> 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 UsedSchema Move Procedure

---------------------------------------- -------------------- ---------------------------------------

SM/AWR 19.103 SYS

SM/OPTSTAT 0.311 SYS

SM/ADVISOR 0.248 SYS

XDB 0.124 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE

SDO 0.073MDSYS MDSYS.MOVE_SDO

EM 0.045SYSMAN emd_maintenance.move_em_tblspc

XSOQHIST 0.037 SYS DBMS_XSOQ.OlapiMoveProc

AO 0.037SYS DBMS_AW.MOVE_AWMETA

ORDIM/ORDDATA 0.013 ORDDATA ordsys.ord_admin.move_ordim_tblspc

LOGMNR 0.013 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE

AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables

?

?

3. 修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除

SQL> selectdbms_stats.get_stats_history_retention from dual;

?

GET_STATS_HISTORY_RETENTION

---------------------------

31

?

SQL> exec dbms_stats.alter_stats_history_retention(15);

?

PL/SQL procedure successfullycompleted

?

SQL> selectdbms_stats.get_stats_history_retention from dual;

?

GET_STATS_HISTORY_RETENTION

---------------------------

15

?

4. 修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改

?

--检查当前系统的保留时间为8天,1小时采样一次

SQL> select * fromdba_hist_wr_control;

?

DBID SNAP_INTERVAL RETENTION TOPNSQL

------------------------------------------------- -------------------------------------------------

1494575446 +0000001:00:00.0 +0000800:00:00.0 DEFAULT

?

?

--

SQL>

begin

dbms_workload_repository.modify_snapshot_settings(

interval => 60,

retention => 10080,--分钟

topnsql => 100

);

end;

?