笔记：Oracle清理sysaux表空间

1，查看sysaux表空间对象大小

select * from 
(select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name='SYSAUX' 
group by segment_name,tablespace_name order by 2 desc)
  where rownum <=20;

2，执行删除snmpshot

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 30000);

3，收缩高水位

查看分区：
select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';

收缩：
alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_1482382090_0;
alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN;

4，重建索引

查看索引：
select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY';

select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';

重建：
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_1357933872_0;

alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN;