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)