笔记：Oracle清理sysaux表空间

最新推荐文章于 2024-10-18 16:23:10 发布

软茸兔

最新推荐文章于 2024-10-18 16:23:10 发布

阅读量1.3k

点赞数

分类专栏： Oracle 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/Lizi_TT/article/details/128451578

版权

Oracle 专栏收录该内容

49 篇文章 0 订阅

订阅专栏

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;