Oracle表空间满清理方案汇总分享
目录
前言思考
一、第一种增加表空间的数据文件数量达到总容量的提升
二、第二种解决方案针对system和sysaux的操作
2.1SYSTEM表空间优化
2.2sysaux表空间回收
2.2.1针对sysaux的表空间爆满还有第二套方案维护
三、第三种解决方案使用alter tablespace resize更改表空间的数据文件的容量;
编辑
前言思考
刚接触Oracle的项目时候,发现我们业务系统的oracle使用3年了,各个表空间都使用快爆满了。经过一段时间的研究分享两套解决方案给大家,首先分两种表空间的扩容和回收。
表空间的分类
第一种表空间是系统自带的表空间system和sysaux,每个表空间的作用和说明,可以翻阅Oracle的官方文档。第二种表空间是我们业务系统创建的表空间用于存放数据的了,比如HS_RISK_DATAHS_INFO_IDX等,是使用数据存储时候表空间会自动增加和扩容。
表空间的扩容和回收
第一种解决方案是在磁盘还有一定量的空间情况下面增加表空间的数据文件,让表空间的数据总量增大。因为增加表空间的数据文件是占磁盘存储量。针对SYSTEM,SYSAUX和HS_RISK_DATA、HS_INFO_IDX的表空间都适用,并且每日的数据都会双向写入表空。
第二种解决方案针对系统自带表空间system和sysaux的操作,针对system的表空间是减少用到的SM/AWR组件表使用空间,使用truncate对表空间收缩,针对sysaux的表空间爆满是删除AWR数据,在通过Move操作回收表的水平线,在重建表的索引。
针对sysaux的表空间爆满还有第三套方案:删除EM相关的内容统计信息,AWR快照、审计信息等。利用SQL语句找出快照最小最大的ID进行删除快照。
第三种解决方案在磁盘空间充沛的情况下面,使用alter tablespace resize更改表空间的数据文件的容量。最好每次增加的容量在1g-2g之间,因为一下增加太多,系统磁盘空间需要划分更多存储空间过去,并且耗费时间。
实验环节展示一下三种扩容方案
实验环境配置:centos6.9, Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
一、第一种增加表空间的数据文件数量达到总容量的提升
使用下列SQL语句查询表空间使用率和空闲率(注保证磁盘存储空间足够多,每增一个数据文件会占据存储空间值)。
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",F.TOTAL_BYTES "空闲空间(M)",F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
记录一下没增加数据文件之前的参数值。待会增加以后表空间使用比会降下来
使用SQL语句查询表空间的数据文件存放的位置。
--查询表空间各个数据文件路径
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
使用SQL在表空间存放在位置添加数据文件,注意按照顺序增加,并且size参数后面增加数据文件容量最好是按照Gb增加,避免增加过多数据文件。并且自动满后扩展到8Gb接触限制条件,无限增长了。
--增加表空间数据文件路径
alter tablespace SYSAUX add datafile '/u01/oradata/uatdb/sysaux02.dbf' size 2G autoextend on next 8G maxsize unlimited;
alter tablespace SYSTEM add datafile '/u01/oradata/uatdb/system02.dbf' size 2G autoextend on next 8G maxsize unlimited;
alter tablespace HS_HIS_DATA add datafile '/u01/oradata/uatdb/hisdat02.dbf' size 2G autoextend on next 8G maxsize unlimited;
alter tablespace HS_HIS_IDX add datafile '/u01/oradata/uatdb/hisidx02.dbf' size 2G autoextend on next 8G maxsize unlimited;
增加表空间的数据文件以后,在使用SQL查询之后,表空间的数据文件会增加一个。
再次查询总的表空间容量参数之后,表空间使用比会降低。表空间容量会上涨了。不管是系统表空间还是业务表空间都得到缓解了。
centos6.9系统中设置oracle中表空间的配置文件的路径也会随着数据文件增加,容量上涨。
二、第二种解决方案针对system和sysaux的操作
2.1SYSTEM表空间优化
首先我们先用SQL语句查询SYSTEM和SYSAUX用了多少表空间。基本上都快爆满了,sysaux占据97.22%,system占据97.03%记得这个数值。后期优化时候,这个数值会下来
SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants where occupant_name='SM/AWR';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');
下面查看下使用SYSTEM和SYSAUX表空间的比较大的表有哪些。
可见,大表大部分都是AUD$和WRH$开头的AWR基表,AUD$使用SYSTEM表空间,AWR的基表使用SYSAUX表空间,下面再查看下SYSAUX表空间的使用情况,可以通过v$sysaux_occupants视图查询到。
可见AUD组件就使用了将近6GB的SYSAUX表空间,也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间,而这些数据是可以定期清理的,都没有必要保留太长的时间。
下面先清理审计的数据,如果要保留部分AUD$里面记录的审计数据,可以把想要的数据插入到一张临时表,然后直接truncate这张表就可以了,truncate操作会直接回收AUD$占用的空间。
truncate操作(需要用SYSDBA权限来执行)
执行完truncate操作后,system的表空间会降低很多。因为大部分AUD$里面记录的审计数据会删除掉
2.2sysaux表空间回收
可见SM/AWR组件就使用了将近6GB的SYSAUX表空间,也就是说审计和AWR占用了大量的SYSAUX表空间。
下面再来回收下SYSAUX表空间,这个相对比较麻烦,也比较耗时。
如上文所示,通过查看v$sysaux_occupants视图,可以确定占用SYSAUX表空间过多的大部分都是AWR的基表,这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间,通常AWR的数据都会设置保留期限,10g版本默认保留7天,11g版本默认保留8天,可以通过dba_hist_wr_control视图来查看(注:并不是所有DBA开头的表都是数据字典,也有很多是视图,
大家可能会有疑问了,AWR的数据既然只保留七八天,为什么还会占用这么多的SYSAUX表空间呢?这个问题我个人认为主要有以下两个原因,首先,AWR删除过期的数据是通过DELETE操作完成的,这样就会产生大量的碎片,特别是SYSAUX表空间存在自动扩展的数据文件,而且这个数据文件没有扩展到最大,还有扩展的空间情况下会很明显,其次就是ASH的数据有些情况下是不受AWR的保留策略影响的,这个从下面的SQL就可以看出。
可以看到,ASH的数据从第一个快照开始一直都在保留,导致WRH$_ACTIVE_SESSION_HISTORY表很大,使用DBMS_WORKLOAD_REPOSITORY包清理过期或者不需要的AWR数据,可以回收这部分空间。(注意SQL语句中snap_id的最高值,一定根据自己筛查出来的结果定值。)
清理了AWR数据之后,你会发现SYSAUX表空间的空间并没有被回收,使用率还和之前一样,这是因为清理AWR操作是通过DELETE操作实现的,表的水位线并没有下降导致的。下面通过MOVE操作回收这个表的水位线,来回收这部分被删除数据占用的空间。
(注意PARTITION_NAME中的取值范围,不能单纯复制粘贴,注意我的结尾880025_0)
下面按照分区进行MOVE操作,来回收空间
对分区表进行MOVE之后,需要重建索引,查看这个表的索引信息。
再次查看,WRH$_ACTIVE_SESSION_HISTORY的空间已经回收。
回收表空间WRH$_ACTIVE_SESSION_HISTORY后,总体的SYSAUX的表空间下降些许。原来是95.02%
通过v$sysaux_occupants视图,可以查询到AWR占用空间由之前的将近6GB降为了不到5GB。按照同样的方法,回收下WRH$_EVENT_HISTOGRAM表的空间在通过回收下WRH$_EVENT_HISTOGRAM表的空间(下图1是找到WRH$_EVENT_HISTOGRAM所在索引)
同样的方法回收表空间,在进行重铸索引。
回收下WRH$_EVENT_HISTOGRAM表的空间,剩下的SYSAUX表空间数据
2.2.1针对sysaux的表空间爆满还有第二套方案维护
SYSAUX表空间做为SYSTEM表空间的辅助表空间,主要存放EM相关的内容以及表统计信息,AWR快照,审计信息等。修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除。
修改AWR快照的保存的参数(我这里没有执行成功,可能被某个Oracle参数限制住了)
---修改AWR快照的保存时间为7天(72460),每小时收集一次
begin dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 10080, topnsql => 100 );
查询最最小和最大快照ID:(楼上我操作删除快一次快照,到22513,现在最小的快照值为22514开始)
通过执行下图的SQL语句,按照快照最小,最大值来进行删除
三、第三种解决方案使用alter tablespace resize更改表空间的数据文件的容量;
先查询表空间里面有多少的数据文件值,知道数据文件的名字
通过下列语句,可以看到每个表空间的数据文件容量为多少了。
select a.file# as "数据文件id",a.name as "数据文件路径",a.bytes / 1024 / 1024 as "当前数据文件大小(MB)",ceil(HWM * a.block_size) / 1024 / 1024 as "可调整至大小(MB)",(a.bytes - HWM * a.block_size) / 1024 / 1024 AS "释放空间大小(MB)",'alter database datafile ''' || a.name || ''' resize ' ||ceil(ceil(HWM * a.block_size) / 1024 / 1024) || 'M;' as "SQL语句"from v$datafile a,(SELECT file_id, MAX(block_id + blocks - 1) HWMFROM DBA_EXTENTSGROUP BY file_id) bwhere a.file# = b.file_id(+)And (a.bytes - HWM * a.block_size) > 0and rownum < 30order by "释放空间大小(MB)" desc
筛查一个表空间的数据文件增加容量到2GB
执行结束,验证表空间的额数据文件已经增加到2GB的容量。
以上是我总结的三种扩容表空间的方法。感谢JiekeXu_DBA,IT邦德老师,还有无数DBA的工程支持。
参考文献:
https://www.cnblogs.com/yaenli/p/16662103.html
oracle system和sysaux表空间清理和回收_oracle system表空间清理-CSDN博客
相关文章:

Oracle表空间满清理方案汇总分享
目录 前言思考 一、第一种增加表空间的数据文件数量达到总容量的提升 二、第二种解决方案针对system和sysaux的操作 2.1SYSTEM表空间优化 2.2sysaux表空间回收 2.2.1针对sysaux的表空间爆满还有第二套方案维护 三、第三种解决方案使用alter tablespace resize更改表空间的…...

基于单片机数码管20V电压表仿真设计
**单片机设计介绍,基于单片机数码管20V电压表仿真设计 文章目录 一 概要二、功能设计设计思路 三、 软件设计原理图 五、 程序六、 文章目录 一 概要 基于单片机数码管20V电压表仿真设计的主要目的是通过单片机和数码管显示电路实现一个能够测量0到20V直流电压的电…...

SCI一区 | Matlab实现NGO-TCN-BiGRU-Attention北方苍鹰算法优化时间卷积双向门控循环单元融合注意力机制多变量时间序列预测
SCI一区 | Matlab实现NGO-TCN-BiGRU-Attention北方苍鹰算法优化时间卷积双向门控循环单元融合注意力机制多变量时间序列预测 目录 SCI一区 | Matlab实现NGO-TCN-BiGRU-Attention北方苍鹰算法优化时间卷积双向门控循环单元融合注意力机制多变量时间序列预测预测效果基本介绍模型…...

C++——优先级队列
前言:这篇文章我们继续来分享一个c的容器——优先级队列。 一.理解优先级 何为优先级一说?实际上就是有顺序的意思。 优先级队列,即有顺序的队列,是一个无需我们自己进行排序操作,在数据传入时就会由容器自己排好序的…...
docker部署jumpserver
1、安装Docker以及相关依赖 配置yum源 sudo yum install -y yum-utils sudo yum-config-manager \ --add-repo \ http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo sudo yum install docker-ce docker-ce-cli containerd.io docker-compose-plugin2、添加国…...

ARM FVP平台的terminal窗口大小如何设置
当启动ARM FVP平台时,terminal窗口太小怎么办?看起来非常累眼睛,本博客来解决这个问题。 首先看下ARM FVP平台对Host主机的需求: 通过上图可知,UART默认使用的是xterm。因此,我们需要修改xterm的默认字体设…...
003 静态代理
文章目录 StudentServiceImplStudentService.javaStudentServiceProxy.javaStudentServiceProxy1.javaStudentServiceProxyTest.java StudentServiceImpl package com.aistart.service.impl;import com.aistart.mapper.StudentMapper; import com.aistart.pojo.Student; import…...
基于JAX的二阶优化方法的实践
使用协作分支上的算法 git clone https://github.com/linjing-lab/jax.git cd jax git checkout linjing-lab cd examples在命令行预览方法 牛顿方法: cat newton_method.py拟牛顿法: cat bfgs_method.py在命令行运行程序 python newton_method.pyp…...

【计算机考研】408算法大题怎么练?
先说结论:基础阶段学好各个数据结构与,重点是数组、链表、树、图。然后强化阶段突破算法提 在基础阶段,并不需要过于专门地练习算法。相反,基础阶段的重点应该放在对各种数据结构原理的深入理解上。在我个人的经验中,…...
输入框验证数字类型
校验大于0的数,且小数点后最多为八位小数 let k /^(?!0(\.0)?$)\d(\.\d{1,8})?$/; console.log(k.test(0.00000001)); // true console.log(k.test(0.00000000)); // false console.log(k.test(0.12)); // true console.log(k.test(12.12)); // true输入0-1的数字…...

LeetCode 377——组合总和 Ⅳ
阅读目录 1. 题目2. 解题思路3. 代码实现 1. 题目 2. 解题思路 此题一看应该就是需要用到动态规划算法,假设我们以 f[d]表示总和为 d 的元素组合的个数,首先,我们遍历 nums 数组, 如果有 nums[i] < target,那么组…...
ubuntu同步网络时间
安装ntpdate sudo apt-get update sudo apt-get install ntpdate设置系统时间与网络时间同步 sudo ntpdate cn.pool.ntp.org设置时区亚洲上海 sudo cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime设置时间为24小时制 echo "LC_TIMEen_DK.UTF-8" >>/…...

Flink学习(四)-数据管道 ETL
一、状态转换 map() 只适用于一对一的转换,即对每个进入算子的流元素,map() 将仅输出一个转换后的元素。 flatmap() 可以输出任意数量的元素,也可以一个都不发。 二、Keyed Streams keyBy() 相当于 sql 中的 group by,通过…...

Python可视化之Matplotlib
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 文章目录 前言1、解决坐标轴刻度负号乱码2、解决中文乱码问题3、图形展现形式 一、图形绘制1.折线图plot2.散点图plot&scatter3.柱状图plt.bar&条形图plt.barh4.直方…...

ChatGPT全方位解析:如何培养 AI 智能对话技能?
简介 ChatGPT 的主要优点之一是它能够理解和响应自然语言输入。在日常生活中,沟通本来就是很重要的一门课程,沟通的过程中表达的越清晰,给到的信息越多,那么沟通就越顺畅。 和 ChatGPT 沟通也是同样的道理,如果想要C…...
[C++/Linux] UDP编程
一. UDP函数 UDP(用户数据报协议,User Datagram Protocol)是一种无连接的网络协议,用于在互联网上交换数据。它允许应用程序发送数据报给另一端的应用程序,但不保证数据报能成功到达,也就是说,它…...
深入探索Linux的lsof命令
在Linux系统中,了解哪些文件被哪些进程打开对于系统管理和问题诊断是极其重要的。这正是lsof命令,即List Open Files,发挥其强大功能的场景。本文旨在详细介绍lsof的起源、底层原理、参数意义,常见用法,并详解其返回结…...
flowable 想改变正在运行的任务,实例版本为最新,需要改哪些表
在Flowable中,要改变正在运行的任务,你需要更新相关的流程定义,具体来说,可能涉及到以下几张表: ACT_RU_TASK(运行时任务):这张表包含了当前正在运行的任务信息。你可能需要更新该表…...
统计各位数字都不同的数字个数 II
3032. 统计各位数字都不同的数字个数 II 给你两个 正整数 a 和 b ,返回 闭区间 [a, b] 内各位数字都不同的数字个数。 示例 1: 输入:a 1, b 20 输出:19 解释:除 11 以外,区间 [1, 20] 内的所有数字的各…...

Taro框架中的H5 模板基本搭建
1.H5 模板框架的搭建 一个h5 的基本框架的搭建 基础template 阿乐/H5 Taro 的基础模板...

装饰模式(Decorator Pattern)重构java邮件发奖系统实战
前言 现在我们有个如下的需求,设计一个邮件发奖的小系统, 需求 1.数据验证 → 2. 敏感信息加密 → 3. 日志记录 → 4. 实际发送邮件 装饰器模式(Decorator Pattern)允许向一个现有的对象添加新的功能,同时又不改变其…...

深入剖析AI大模型:大模型时代的 Prompt 工程全解析
今天聊的内容,我认为是AI开发里面非常重要的内容。它在AI开发里无处不在,当你对 AI 助手说 "用李白的风格写一首关于人工智能的诗",或者让翻译模型 "将这段合同翻译成商务日语" 时,输入的这句话就是 Prompt。…...
在 Nginx Stream 层“改写”MQTT ngx_stream_mqtt_filter_module
1、为什么要修改 CONNECT 报文? 多租户隔离:自动为接入设备追加租户前缀,后端按 ClientID 拆分队列。零代码鉴权:将入站用户名替换为 OAuth Access-Token,后端 Broker 统一校验。灰度发布:根据 IP/地理位写…...

高危文件识别的常用算法:原理、应用与企业场景
高危文件识别的常用算法:原理、应用与企业场景 高危文件识别旨在检测可能导致安全威胁的文件,如包含恶意代码、敏感数据或欺诈内容的文档,在企业协同办公环境中(如Teams、Google Workspace)尤为重要。结合大模型技术&…...
Caliper 配置文件解析:config.yaml
Caliper 是一个区块链性能基准测试工具,用于评估不同区块链平台的性能。下面我将详细解释你提供的 fisco-bcos.json 文件结构,并说明它与 config.yaml 文件的关系。 fisco-bcos.json 文件解析 这个文件是针对 FISCO-BCOS 区块链网络的 Caliper 配置文件,主要包含以下几个部…...

排序算法总结(C++)
目录 一、稳定性二、排序算法选择、冒泡、插入排序归并排序随机快速排序堆排序基数排序计数排序 三、总结 一、稳定性 排序算法的稳定性是指:同样大小的样本 **(同样大小的数据)**在排序之后不会改变原始的相对次序。 稳定性对基础类型对象…...
纯 Java 项目(非 SpringBoot)集成 Mybatis-Plus 和 Mybatis-Plus-Join
纯 Java 项目(非 SpringBoot)集成 Mybatis-Plus 和 Mybatis-Plus-Join 1、依赖1.1、依赖版本1.2、pom.xml 2、代码2.1、SqlSession 构造器2.2、MybatisPlus代码生成器2.3、获取 config.yml 配置2.3.1、config.yml2.3.2、项目配置类 2.4、ftl 模板2.4.1、…...

RabbitMQ入门4.1.0版本(基于java、SpringBoot操作)
RabbitMQ 一、RabbitMQ概述 RabbitMQ RabbitMQ最初由LShift和CohesiveFT于2007年开发,后来由Pivotal Software Inc.(现为VMware子公司)接管。RabbitMQ 是一个开源的消息代理和队列服务器,用 Erlang 语言编写。广泛应用于各种分布…...

【LeetCode】算法详解#6 ---除自身以外数组的乘积
1.题目介绍 给定一个整数数组 nums,返回 数组 answer ,其中 answer[i] 等于 nums 中除 nums[i] 之外其余各元素的乘积 。 题目数据 保证 数组 nums之中任意元素的全部前缀元素和后缀的乘积都在 32 位 整数范围内。 请 不要使用除法,且在 O…...
鸿蒙HarmonyOS 5军旗小游戏实现指南
1. 项目概述 本军旗小游戏基于鸿蒙HarmonyOS 5开发,采用DevEco Studio实现,包含完整的游戏逻辑和UI界面。 2. 项目结构 /src/main/java/com/example/militarychess/├── MainAbilitySlice.java // 主界面├── GameView.java // 游戏核…...