文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

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

概述

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

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

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

SELECT *  FROM (SELECT D.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 (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,  SUM(BLOCKS) BLOCKS  FROM DBA_DATA_FILES  GROUP BY TABLESPACE_NAME) D,  (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE  FROM DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  UNION ALL  SELECT D.TABLESPACE_NAME,  SPACE || 'M' "SUM_SPACE(M)",  BLOCKS SUM_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 (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,  SUM(BLOCKS) BLOCKS  FROM DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) D,  (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  FROM V$TEMP_SPACE_HEADER  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  ORDER BY 1)  WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

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

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

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

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

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

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

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

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

SELECT occupant_name "Item",  space_usage_kbytes / 1048576 "Space Used (GB)",  schema_name "Schema",  move_procedure "Move Procedure"  FROM v$sysaux_occupants  ORDER BY space_usage_kbytes desc;

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

可见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就是视图)

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

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

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

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

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

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

select distinct 'truncate table ' || segment_name || ';',  s.bytes/1024/1024 MB  from dba_segments s  where s.segment_name like 'WRH$%'  and segment_type in ('TABLE PARTITION', 'TABLE')  and s.bytes/1024/1024 >100  order by s.bytes/1024/1024 desc;

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

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

truncate table WRH$_SQL_BIND_METADATA; &mdash;-保存AWR收集SQL绑定信息表 truncate table WRH$_ACTIVE_SESSION_HISTORY; &mdash;&mdash;保存AWR收集历史会话信息表 select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in('WRH$_LATCH','WRH$_SQLSTAT'); alter table WRH$_LATCH truncate partition WRH$_LATCH_1013373590_0; alter table WRH$_SQLSTAT truncate partition WRH$_SQLSTA_1013373590_0; truncate table WRI$_ADV_SQLT_PLANS; &mdash;保存AWR收集SQL建议计划信息表 alter table WRH$_SYSSTAT shrink space; alter index WRH$_SYSSTAT_PK shrink space; alter table WRH$_LATCH shrink space; alter table WRH$_SEG_STAT shrink space; alter table WRH$_SQLSTAT shrink space; alter table WRH$_PARAMETER shrink space; &mdash;-保存AWR收集参数信息表 alter index WRH$_PARAMETER_PK shrink space;  truncate table WRH$_EVENT_HISTOGRAM; truncate table WRH$_SQL_PLAN; &mdash;-保存AWR收集的SQL执行计划表 truncate table WRH$_SQLTEXT; &ndash;&mdash;保存AWR收集的SQL文本表

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

6. 验证

达到需求,完。

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

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯