SYSAUX表空间清理之WRH$_ACTIVE_SESSION_HISTORY

查看sysaux表空间使用率高，对于sysaux表空间之前有文章讨论过，本次直入正题。
1.检查sysaux表空间占用空间较大的segments。

SQL> select * from (
select owner,
       segment_name,
       segment_type,
       sum(bytes) / 1024 / 1024 / 1024 GB
  from dba_segments
 where tablespace_name='SYSAUX' 
 group by owner, segment_name, segment_type
 order by 4 desc ) where rownum <20;
  2    3    4    5    6    7    8    9  

OWNER			       SEGMENT_NAME									 SEGMENT_TYPE		    GB
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
SYS			       WRH$_ACTIVE_SESSION_HISTORY							 TABLE PARTITION    31.3724365
CTXSYS			       SYS_IOT_TOP_3836231								 INDEX		    6.53265381
CTXSYS			       SYS_IOT_TOP_55848								 INDEX		    5.51123047
SYS			       WRH$_ACTIVE_SESSION_HISTORY_PK							 INDEX PARTITION    3.96099854
SYS			       WRH$_EVENT_HISTOGRAM_PK								 INDEX PARTITION    2.56646729
SYS			       WRH$_EVENT_HISTOGRAM								 TABLE PARTITION    2.31842041
SYS			       WRH$_LATCH_MISSES_SUMMARY_PK							 INDEX PARTITION     1.1260376
SYS			       WRH$_LATCH_MISSES_SUMMARY							 TABLE PARTITION    1.06939697
SYS			       WRH$_SQLSTAT									 TABLE PARTITION     .95123291
SYS			       WRH$_LATCH									 TABLE PARTITION     .69342041
SYS			       WRH$_SYSSTAT_PK									 INDEX PARTITION    .575256348

OWNER			       SEGMENT_NAME									 SEGMENT_TYPE		    GB
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
SYS			       WRH$_LATCH_PK									 INDEX PARTITION     .49420166
SYS			       WRH$_SEG_STAT									 TABLE PARTITION    .445373535
SYS			       WRH$_SYSSTAT									 TABLE PARTITION    .445373535
SYS			       WRH$_SYSTEM_EVENT								 TABLE PARTITION    .426818848
SYS			       WRM$_SNAPSHOT_DETAILS_INDEX							 INDEX		    .407348633
SYS			       WRH$_PARAMETER_PK								 INDEX PARTITION    .371154785
SYS			       WRH$_PARAMETER									 TABLE PARTITION    .343811035
SYS			       WRM$_SNAPSHOT_DETAILS								 TABLE		    .326171875

19 rows selected.

2.检查表WRH$_ACTIVE_SESSION_HISTORY的分区情况。

SQL> select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' and segment_name='WRH$_ACTIVE_SESSION_HISTORY' order by 3; 

SEGMENT_NAME									  PARTITION_NAME		 SEGMENT_TYPE	    BYTES/1024/1024
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------------
WRH$_ACTIVE_SESSION_HISTORY							  WRH$_ACTIVE_1400637121_77293	 TABLE PARTITION	 32125.3125
WRH$_ACTIVE_SESSION_HISTORY							  WRH$_ACTIVE_SES_MXDB_MXSN	 TABLE PARTITION	      .0625

3.truncate 分区WRH$_ACTIVE_1400637121_77293。

SQL> alter table sys.wrh$_active_session_history truncate partition  WRH$_ACTIVE_1400637121_77293 update global indexes;

Table truncated.

4.修改参数，使其新建分区。可以看到新建了新的分区。

SQL> select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' and segment_name='WRH$_ACTIVE_SESSION_HISTORY' order by 3; 

SEGMENT_NAME									  PARTITION_NAME		 SEGMENT_TYPE	    BYTES/1024/1024
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------------
WRH$_ACTIVE_SESSION_HISTORY							  WRH$_ACTIVE_SES_MXDB_MXSN	 TABLE PARTITION	      .0625
WRH$_ACTIVE_SESSION_HISTORY							  WRH$_ACTIVE_1400637121_77293	 TABLE PARTITION	      .0625
WRH$_ACTIVE_SESSION_HISTORY							  WRH$_ACTIVE_1400637121_88057	 TABLE PARTITION	      .0625

检查表空间使用率，已经降下来了

SQL> @tbs

status	  Tablespace Name	    TS Type	 Ext. Mgt.  Seg. Mgt. Tablespace Size(GB)    Free (GB)	  Used (GB)  Pct. Used
--------- ------------------------- ------------ ---------- --------- ------------------- ------------ ------------ ----------
ONLINE	  SYSAUX		    PERMANENT	 LOCAL	    AUTO		       68	    38		 31	    45