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 的基础模板...

网络编程(Modbus进阶)
思维导图 Modbus RTU(先学一点理论) 概念 Modbus RTU 是工业自动化领域 最广泛应用的串行通信协议,由 Modicon 公司(现施耐德电气)于 1979 年推出。它以 高效率、强健性、易实现的特点成为工业控制系统的通信标准。 包…...
Python|GIF 解析与构建(5):手搓截屏和帧率控制
目录 Python|GIF 解析与构建(5):手搓截屏和帧率控制 一、引言 二、技术实现:手搓截屏模块 2.1 核心原理 2.2 代码解析:ScreenshotData类 2.2.1 截图函数:capture_screen 三、技术实现&…...

Spark 之 入门讲解详细版(1)
1、简介 1.1 Spark简介 Spark是加州大学伯克利分校AMP实验室(Algorithms, Machines, and People Lab)开发通用内存并行计算框架。Spark在2013年6月进入Apache成为孵化项目,8个月后成为Apache顶级项目,速度之快足见过人之处&…...

Qt/C++开发监控GB28181系统/取流协议/同时支持udp/tcp被动/tcp主动
一、前言说明 在2011版本的gb28181协议中,拉取视频流只要求udp方式,从2016开始要求新增支持tcp被动和tcp主动两种方式,udp理论上会丢包的,所以实际使用过程可能会出现画面花屏的情况,而tcp肯定不丢包,起码…...

Linux相关概念和易错知识点(42)(TCP的连接管理、可靠性、面临复杂网络的处理)
目录 1.TCP的连接管理机制(1)三次握手①握手过程②对握手过程的理解 (2)四次挥手(3)握手和挥手的触发(4)状态切换①挥手过程中状态的切换②握手过程中状态的切换 2.TCP的可靠性&…...

Python实现prophet 理论及参数优化
文章目录 Prophet理论及模型参数介绍Python代码完整实现prophet 添加外部数据进行模型优化 之前初步学习prophet的时候,写过一篇简单实现,后期随着对该模型的深入研究,本次记录涉及到prophet 的公式以及参数调优,从公式可以更直观…...

SpringCloudGateway 自定义局部过滤器
场景: 将所有请求转化为同一路径请求(方便穿网配置)在请求头内标识原来路径,然后在将请求分发给不同服务 AllToOneGatewayFilterFactory import lombok.Getter; import lombok.Setter; import lombok.extern.slf4j.Slf4j; impor…...

Redis数据倾斜问题解决
Redis 数据倾斜问题解析与解决方案 什么是 Redis 数据倾斜 Redis 数据倾斜指的是在 Redis 集群中,部分节点存储的数据量或访问量远高于其他节点,导致这些节点负载过高,影响整体性能。 数据倾斜的主要表现 部分节点内存使用率远高于其他节…...
DeepSeek 技术赋能无人农场协同作业:用 AI 重构农田管理 “神经网”
目录 一、引言二、DeepSeek 技术大揭秘2.1 核心架构解析2.2 关键技术剖析 三、智能农业无人农场协同作业现状3.1 发展现状概述3.2 协同作业模式介绍 四、DeepSeek 的 “农场奇妙游”4.1 数据处理与分析4.2 作物生长监测与预测4.3 病虫害防治4.4 农机协同作业调度 五、实际案例大…...

Selenium常用函数介绍
目录 一,元素定位 1.1 cssSeector 1.2 xpath 二,操作测试对象 三,窗口 3.1 案例 3.2 窗口切换 3.3 窗口大小 3.4 屏幕截图 3.5 关闭窗口 四,弹窗 五,等待 六,导航 七,文件上传 …...