AWR数据增长较快，导致sysaux表空间使用较高
SQL> select f.tablespace_name, 2 a.total, 3 f.free, 4 round((f.free / a.total) * 100) "% Free" 5 from (select tablespace_name, sum(bytes / (1024 * 1024)) total 6 from dba_data_files 7 group by tablespace_name) a, 8 (select tablespace_name, round(sum(bytes / (1024 * 1024))) free 9 from dba_free_space 10 group by tablespace_name) f 11 WHERE a.tablespace_name = f.tablespace_name(+) 12 order by "% Free" 13 / TABLESPACE_NAME TOTAL FREE % Free ------------------------------ ---------- ---------- ---------- UNDOTBS1 5185 6 0 SYSAUX 8660 413 5 USERS 5 4 80 SYSTEM 4640 3798 82 UNDOTBS2 365 335 92 X_DATA 1000 990 99 X_INDEX 1000 986 99
查看是哪些数据库对象占用了sysaux表空间
SQL> select * 2 from (select segment_name, 3 PARTITION_NAME, 4 segment_type, 5 bytes / 1024 / 1024 6 from dba_segments 7 where tablespace_name = 'SYSAUX' 8 order by 4 desc ) 9 where rownum <= 20; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024 ------------------------------ ------------------------------ ------------------ --------------- WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__1402125233_0 INDEX PARTITION 736 WRH$_EVENT_HISTOGRAM WRH$_EVENT__1402125233_0 TABLE PARTITION 658 WRH$_LATCH WRH$_LATCH_1402125233_0 TABLE PARTITION 588 WRH$_SQLSTAT WRH$_SQLSTA_1402125233_0 TABLE PARTITION 560 WRH$_SYSSTAT_PK WRH$_SYSSTA_1402125233_0 INDEX PARTITION 504 WRH$_LATCH_PK WRH$_LATCH_1402125233_0 INDEX PARTITION 440 WRH$_SYSSTAT WRH$_SYSSTA_1402125233_0 TABLE PARTITION 368 WRH$_PARAMETER_PK WRH$_PARAME_1402125233_0 INDEX PARTITION 336 WRH$_PARAMETER WRH$_PARAME_1402125233_0 TABLE PARTITION 280 WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__1402125233_0 INDEX PARTITION 280 WRH$_SEG_STAT WRH$_SEG_ST_1402125233_0 TABLE PARTITION 256 WRH$_SYSTEM_EVENT WRH$_SYSTEM_1402125233_0 TABLE PARTITION 232 WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1402125233_0 TABLE PARTITION 224 WRH$_SYSTEM_EVENT_PK WRH$_SYSTEM_1402125233_0 INDEX PARTITION 216 WRH$_SQLSTAT_PK WRH$_SQLSTA_1402125233_0 INDEX PARTITION 160 WRH$_SERVICE_STAT_PK WRH$_SERVIC_1402125233_0 INDEX PARTITION 152 WRH$_DLM_MISC WRH$_DLM_MI_1402125233_0 TABLE PARTITION 120 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1402125233_0 TABLE PARTITION 120 WRH$_SQLSTAT_INDEX WRH$_SQLSTA_1402125233_0 INDEX PARTITION 112 WRH$_SEG_STAT_PK WRH$_SEG_ST_1402125233_0 INDEX PARTITION 104
查看awr快照保留期限。设置的期限是8天，但是真正保留了258天
SQL> select sysdate - a.sample_time ash, 2 sysdate - s.begin_interval_time snap, 3 c.RETENTION 4 from sys.wrm$_wr_control c, 5 (select db.dbid, min(w.sample_time) sample_time 6 from sys.v_$database db, sys.Wrh$_active_session_history w 7 where w.dbid = db.dbid 8 group by db.dbid) a, 9 (select db.dbid, min(r.begin_interval_time) begin_interval_time 10 from sys.v_$database db, sys.wrm$_snapshot r 11 where r.dbid = db.dbid 12 group by db.dbid) s 13 where a.dbid = s.dbid 14 and c.dbid = a.dbid; ASH SNAP RETENTION ---------------------------------------- ---------------------------------------- ------------------------------------- +000000258 08:55:03.824 +000000008 08:55:30.192 +00008 00:00:00.0
也可以通过dba_hist_snapshot和WRH$_EVENT_HISTOGRAM进行比较
SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
5489 6350
SQL> select min(snap_id),MAX(snap_id) from WRH$_EVENT_HISTOGRAM;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
168 6350
SQL>
通过MOS查看，这是数据库的一个bug：
Document 14084247.8 Bug 14084247 - Failed AWR purge can lead to continued SYSAUX space use
因为purge操作失败，split分区表没有成功。所以导致分区大小不断增加，进而影响性能（purge job超时，导致AWR过期数据没有被清空）。
但是，即使是安装了补丁后，也不是立即生效，可能需要等一段时间才能生效。
安装了补丁后，还要按照以下步骤执行purge操作：
1) drop expired partitions (DDL)
2) purge by callback (anything can happen here, but mostly no-ops or regular DELETE operations)
3) purge regular non-partitioned tables (i.e, DELETE)
4) split partitions (DDL)