WR手工创建快照失败，SYSAUX表空间剩余不足处理

案例：AWR手工创建快照失败，SYSAUX表空间剩余不足处理

版本：Oracle 11.2.0.4 RAC
问题现象：AWR手工创建快照失败，SYSAUX表空间剩余不足。

1. 查看SYSAUX表空间的段大小

查看SYSAUX表空间的段大小，按大小排序：

select segment_name, sum(bytes) / 1024 / 1024 from dba_segments where tablespace_name = 'SYSAUX' group by segment_name order by 2;

最大的结果列表：

SEGMENT_NAME SUM(BYTES)/1024/1024 ------------------------------ -------------------- WRH$_SYSMETRIC_HISTORY_INDEX 80 WRH$_DLM_MISC_PK 80.0625 WRH$_DLM_MISC 96.0625 WRH$_EVENT_HISTOGRAM 688.0625 WRH$_EVENT_HISTOGRAM_PK 1008.0625 WRH$_ACTIVE_SESSION_HISTORY_PK 3016.0625 WRH$_ACTIVE_SESSION_HISTORY 22373.0625 4462 rows selected.

发现最大的对象是WRH$_ACTIVE_SESSION_HISTORY，这里占了22G的大小，另外其对应的主键索引也占了3G。

2. AWR快照列表是空

查看AWR列表：

@?/rdbms/admin/awrrpt

AWR快照列表是空

Listing all Completed Snapshots

尝试手工创建快照也会失败，明确提示是SYSAUX表空间剩余不足，无法扩展。

exec DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 3. 清理回收表空间

根据MOS文档 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)

查询表空间使用率：

set lines 400 pages 999 select TABLESPACE_NAME,(TABLESPACE_SIZE-USED_SPACE)*8/1024/1024 free_space, USED_SPACE*8/1024/1024 USED_SPACE,TABLESPACE_SIZE*8/1024/1024 TABLESPACE_SIZE,USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS order by 5 ;

SYSAUX 3.60882568 28.3911591 31.9999847 88.7224144

查询WRH$_ACTIVE_SESSION_HISTORY的分区信息：

SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

结果如下：

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB ------------------------------ ------------------------------ ------------------------------ ------------------ ---------- SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1154052622_0 TABLE PARTITION 21.7900391 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035

根据MOS的说明操作：

alter session set "_swrf_test_action" = 72;

经过这一步之后，发现分割出一个分区，而且实际发现SYSAUX表空间释放出3G的空间，这里思考一下，分割分区并不会导致空间下降，推测有可能是分割的操作触发了自动删除的条件。

结果如下(多了一个分区)：

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB ------------------------------ ------------------------------ ------------------------------ ------------------ ---------- SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1154052622_0 TABLE PARTITION 21.7900391 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1154052622_5093 TABLE PARTITION .05859375 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035

此时，根据MOS文档说明，执行：

set serveroutput on declare CURSOR cur_part IS SELECT partition_name from dba_tab_partitions WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; query1 varchar2(200); query2 varchar2(200); TYPE partrec IS RECORD (snapid number, dbid number); TYPE partlist IS TABLE OF partrec; Outlist partlist; begin dbms_output.put_line('PARTITION NAME SNAP_ID DBID'); dbms_output.put_line('--------------------------- ------- ----------'); for part in cur_part loop query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; execute immediate query1 bulk collect into OutList; if OutList.count > 0 then for i in OutList.first..OutList.last loop dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid); end loop; end if; query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; execute immediate query2 bulk collect into OutList; if OutList.count > 0 then for i in OutList.first..OutList.last loop dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid); dbms_output.put_line('---'); end loop; end if; end loop; end; /