Oracle数据库标准的SYSAUX表空间清理方法是什么

2025-03-07 技术教程

这篇文章将为大家详细讲解有关Oracle数据库标准的SYSAUX表空间清理方法是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

概述

SYSAUX表空间被称为系统辅助表空间，是10g版本开始推出的新功能，主要的目的是为SYSTEM表空间减负，Oracle对SYSTEM表空间的维护有一套独立的体系，对SYSTEM表空间操作会占用额外的CPU资源，而且效率低下。在10g版本，增加了SYSAUX辅助表空间，将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中，这样大大减少了SYSTEM表空间的消耗，也减少了Oracle对SYSTEM表空间维护的成本。

下面介绍下最近清理sysaux表空间的过程，仅供参考。

1. 查询SYSTEM和SYSAUX表空间的使用率

SELECT*FROM(SELECTD.TABLESPACE_NAME,SPACE||'M'"SUM_SPACE(M)",BLOCKS"SUM_BLOCKS",SPACE-NVL(FREE_SPACE,0)||'M'"USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)||'%'"USED_RATE(%)",FREE_SPACE||'M'"FREE_SPACE(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMDBA_DATA_FILESGROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)FWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)UNIONALLSELECTD.TABLESPACE_NAME,SPACE||'M'"SUM_SPACE(M)",BLOCKSSUM_BLOCKS,USED_SPACE||'M'"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)||'%'"USED_RATE(%)",NVL(FREE_SPACE,0)||'M'"FREE_SPACE(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMDBA_TEMP_FILESGROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACEFROMV$TEMP_SPACE_HEADERGROUPBYTABLESPACE_NAME)FWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)ORDERBY1)WHERETABLESPACE_NAMEIN('SYSAUX','SYSTEM');

可见，SYSAUX表空间已经使用了43GB左右，SYSTEM表空间已经使用了3GB左右。

2. 查看下使用SYSTEM和SYSAUX表空间的比较大的表

select*from(selectsegment_name,PARTITION_NAME,sum(bytes)/1024/1024total_mb,tablespace_namefromdba_segmentswheretablespace_namein('SYSTEM','SYSAUX')groupbysegment_name,tablespace_nameorderby3desc)whererownum<=20;

可见，大表大部分都是AUD$和WRH$开头的AWR基表，AUD$使用SYSTEM表空间，AWR的基表使用SYSAUX表空间

3. 查看SYSAUX表空间的具体使用情况

这里可以通过v$sysaux_occupants视图查询到

SELECToccupant_name"Item",space_usage_kbytes/1048576"SpaceUsed(GB)",schema_name"Schema",move_procedure"MoveProcedure"FROMv$sysaux_occupantsORDERBYspace_usage_kbytesdesc;

可见SM/AWR组件就使用了40GB的SYSAUX表空间，也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间，而这些数据是可以定期清理的，都没有必要保留太长的时间。

通过查看v$sysaux_occupants视图，可以确定占用SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间，通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看(注：并不是所有DBA开头的表都是数据字典，也有很多是视图，dba_hist_wr_control就是视图)

问题来了，AWR的数据既然只保留七八天，为什么还会占用这么多的SYSAUX表空间呢?这个问题其实有以下两个原因，首先，AWR删除过期的数据是通过DELETE操作完成的，这样就会产生大量的碎片，特别是SYSAUX表空间存在自动扩展的数据文件，而且这个数据文件没有扩展到最大，还有扩展的空间情况下会很明显，其次就是ASH的数据有些情况下是不受AWR的保留策略影响的。

4. 使用dbms_workload_repository.drop_snapshot_range删除历史数据(不推荐)

--ASH的数据从第一个快照开始一直都在保留，导致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包--清理过期或者不需要的AWR数据，可以回收这部分空间selectcount(*)fromWRH$_ACTIVE_SESSION_HISTORY;selectmin(snap_id),max(snap_id)fromwrh$_active_session_history;execDBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id=>1,high_snap_id=>25100);selectcount(*)fromWRH$_ACTIVE_SESSION_HISTORY;

清理了AWR数据之后，可以发现SYSAUX表空间的空间并没有被回收，使用率还和之前一样，这是因为清理AWR操作是通过DELETE操作实现的，表的水位线并没有下降导致的。

5. 手工生成truncate，需要在SYS下执行

selectdistinct'truncatetable'||segment_name||';',s.bytes/1024/1024MBfromdba_segmentsswheres.segment_namelike'WRH$%'andsegment_typein('TABLEPARTITION','TABLE')ands.bytes/1024/1024>100orderbys.bytes/1024/1024desc;

实际执行sql:通过上面语句可以看到基本上都是以WRH$_开头的段，这些类型的段基本上都是与AWR相关的,以下均以sys用户执行

truncatetableWRH$_SQL_BIND_METADATA;—-保存AWR收集SQL绑定信息表truncatetableWRH$_ACTIVE_SESSION_HISTORY;——保存AWR收集历史会话信息表selectsegment_name,partition_name,bytes/1024/1024/1024gbfromdba_segmentswheresegment_namein('WRH$_LATCH','WRH$_SQLSTAT');altertableWRH$_LATCHtruncatepartitionWRH$_LATCH_1013373590_0;altertableWRH$_SQLSTATtruncatepartitionWRH$_SQLSTA_1013373590_0;truncatetableWRI$_ADV_SQLT_PLANS;—保存AWR收集SQL建议计划信息表altertableWRH$_SYSSTATshrinkspace;alterindexWRH$_SYSSTAT_PKshrinkspace;altertableWRH$_LATCHshrinkspace;altertableWRH$_SEG_STATshrinkspace;altertableWRH$_SQLSTATshrinkspace;altertableWRH$_PARAMETERshrinkspace;—-保存AWR收集参数信息表alterindexWRH$_PARAMETER_PKshrinkspace;truncatetableWRH$_EVENT_HISTOGRAM;truncatetableWRH$_SQL_PLAN;—-保存AWR收集的SQL执行计划表truncatetableWRH$_SQLTEXT;–—保存AWR收集的SQL文本表

6. 验证

达到需求，完。

关于Oracle数据库标准的SYSAUX表空间清理方法是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。