Oracle SYSTEM 和 SYSAUX 表空间的清理和回收

背景介绍

在 Oracle 数据库中，SYSTEM 和 SYSAUX 表空间是两个非常重要的表空间。SYSTEM 表空间主要用于存储数据库的核心元数据，如数据字典信息，及数据库的审计功能开启的话（SYS.AUD$表）。而 SYSAUX 表空间则是 SYSTEM 表空间的辅助表空间，主要用于存储一些辅助组件的数据，如 Enterprise Manager (EM)、Automatic Workload Repository (AWR) 等。

随着时间的推移，这两个表空间可能会变得非常大，尤其是 SYSAUX 表空间，因为它存储了大量的历史数据和统计信息。本文将详细介绍如何清理和回收 SYSTEM 和 SYSAUX 表空间的空间。

当前表空间使用情况

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

首先，查询 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) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALLSELECT 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) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

查询 SYSTEM 和 SYSAUX 表空间中较大的表

接下来，查询 SYSTEM 和 SYSAUX 表空间中占用空间较大的表：

SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB, TABLESPACE_NAMEFROM DBA_SEGMENTSWHERE TABLESPACE_NAME IN ('SYSTEM', 'SYSAUX')GROUP BY SEGMENT_NAME, TABLESPACE_NAMEORDER BY 2 DESC
)
WHERE ROWNUM <= 20;

清理 SYSTEM 表空间

清理审计表 AUD$

  1. 查询审计数据

    SELECT t.owner, t.segment_name, SUM(bytes) / 1024 / 1024 / 1024 AS SIZE_G
FROM dba_segments t
WHERE t.tablespace_name = 'SYSTEM' AND t.segment_name = 'AUD$'
GROUP BY t.owner, t.segment_name
ORDER BY SUM(bytes) DESC;-- 备份审计数据
CREATE TABLE AUD_BACKUP AS SELECT * FROM AUD$;

    请注意，如果 AUD$ 表非常大，这个备份审计数据可能会消耗大量的存储空间和时间。因此，在执行此操作前，确保有足够的磁盘空间可用，并且最好选择在系统负载较低的时间段进行。

    如果审计数据确定没用了，可以不进行备份。直接进行如下操作。

  2. 截断审计表

    TRUNCATE TABLE AUD$;

  3. 验证空间回收

    SELECT BYTES / 1024 / 1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'AUD$';

  4. 查看审计功能

    SQL> show parameter audit

  5. 关闭审计功能

    SQL> alter system set audit_trail='none' scope=spfile;

    如果只是清理 AUD$表，问题已经解决，但是时间久后，问题还是会复现，如果不需要审计数据可以关闭审计功能永久解决。关闭审计需要重启数据库。

审计表转移至新表空间

为了避免以后审计表占用大量system表空间，可以考虑将AUD$表迁移到新的表空间。

例如：将 SYSTEM 表空间中的 AUD$ 表转移到新的表空间AUD_TBS

1. 创建新表空间

首先，确保 AUD_TBS 表空间已经存在。如果不存在，可以使用你提供的 PL/SQL 代码块来创建它。

DECLAREv_data_dir VARCHAR2(200);v_sql1 VARCHAR2(1000);v_cnt NUMBER;
BEGIN-- 检查 AUD_TBS 表空间是否存在SELECT COUNT(1) INTO v_cnt FROM dba_data_files WHERE tablespace_name = 'AUD_TBS';IF v_cnt = 0 THEN-- 获取数据文件目录SELECT REPLACE(REPLACE(name, 'system01.dbf', ''), 'SYSTEM01.DBF', '') INTO v_data_dirFROM v$datafileWHERE file# = 1;DBMS_OUTPUT.PUT_LINE('Data directory: ' || v_data_dir);-- 构建创建表空间的 SQL 语句v_sql1 := 'CREATE TABLESPACE aud_tbs DATAFILE ' ||'''' || v_data_dir || 'aud_tbs01.dbf''' || ' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED, ' ||'''' || v_data_dir || 'aud_tbs02.dbf''' || ' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED';DBMS_OUTPUT.PUT_LINE('Creating tablespace with SQL: ' || v_sql1);-- 执行创建表空间的 SQL 语句EXECUTE IMMEDIATE v_sql1;END IF;-- 设置审计跟踪位置DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'AUD_TBS');-- 提交事务COMMIT;DBMS_OUTPUT.PUT_LINE('Audit trail location set to AUD_TBS successfully.');
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);ROLLBACK;
END;
/
2. 移动 AUD$ 表及其索引

接下来，将 AUD$ 表从 SYSTEM 表空间移动到 AUD_TBS 表空间。这包括移动表本身以及相关的索引。

2.1 移动 AUD$ 表
ALTER TABLE SYS.AUD$ MOVE TABLESPACE AUD_TBS;
2.2 移动相关索引

查询 AUD$ 表的所有索引，并逐个移动它们：

-- 查询 AUD$ 表的所有索引
SELECT index_name
FROM dba_indexes
WHERE table_owner = 'SYS' AND table_name = 'AUD$';-- 移动每个索引
BEGINFOR i IN (SELECT index_name FROM dba_indexes WHERE table_owner = 'SYS' AND table_name = 'AUD$') LOOPEXECUTE IMMEDIATE 'ALTER INDEX SYS.' || i.index_name || ' REBUILD TABLESPACE AUD_TBS';DBMS_OUTPUT.PUT_LINE('Index ' || i.index_name || ' moved to AUD_TBS');END LOOP;
END;
/
3. 验证移动结果

验证 AUD$ 表及其索引是否已成功移动到 AUD_TBS 表空间：

-- 检查 AUD$ 表的位置
SELECT segment_name, tablespace_name
FROM dba_segments
WHERE owner = 'SYS' AND segment_name = 'AUD$';-- 检查 AUD$ 表的索引位置
SELECT index_name, tablespace_name
FROM dba_indexes
WHERE table_owner = 'SYS' AND table_name = 'AUD$';

清理 SYSAUX 表空间

查询 SYSAUX 表空间的占用情况

SELECT OCCUPANT_NAME "Item",SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",SCHEMA_NAME "Schema",MOVE_PROCEDURE "Move Procedure"FROM V$SYSaux_OCCUPANTSORDER BY 1;

清理 AWR 数据

  1. 查询 AWR 快照保留时间

    SELECT DBMS_WORKLOAD_REPOSITORY.GET_RETENTION FROM DUAL;

  2. 修改 AWR 快照保留时间

    EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (INTERVAL => 60, RETENTION => 7*24*60, TOPNSQL => 100);
或者
exec dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 15*24*60);

  3. 删除过期的 AWR 快照

    -- 查询现有 AWR 快照：
SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID;
-- 执行删除命令：
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (LOW_SNAP_ID => 1, HIGH_SNAP_ID => 30000);

回收 WRH$_ACTIVE_SESSION_HISTORY 表的空间

  1. 查询 WRH$_ACTIVE_SESSION_HISTORY 表的分区信息

    SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 / 1024 GBFROM DBA_SEGMENTSWHERE SEGMENT_NAME = 'WRH$_ACTIVE_SESSION_HISTORY';

  2. 移动 WRH$_ACTIVE_SESSION_HISTORY 表的分区

    ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_1357933872_0;
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION WRH$_ACTIVE_SES_MXDB_MXSN;

  3. 重建 WRH$_ACTIVE_SESSION_HISTORY 表的索引

    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;

  4. 验证空间回收

    SELECT SUM(BYTES) / 1024 / 1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'WRH$_ACTIVE_SESSION_HISTORY';
SELECT SUM(BYTES) / 1024 / 1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'WRH$_ACTIVE_SESSION_HISTORY_PK';

回收 WRH$_EVENT_HISTOGRAM 表的空间

  1. 查询 WRH$_EVENT_HISTOGRAM 表的分区信息

    SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 / 1024 GBFROM DBA_SEGMENTSWHERE SEGMENT_NAME = 'WRH$_EVENT_HISTOGRAM';

  2. 移动 WRH$_EVENT_HISTOGRAM 表的分区

    ALTER TABLE WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT_HISTO_MXDB_MXSN;
ALTER TABLE WRH$_EVENT_HISTOGRAM MOVE PARTITION WRH$_EVENT__1357933872_0;

  3. 重建 WRH$_EVENT_HISTOGRAM 表的索引

    ALTER INDEX WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT_HISTO_MXDB_MXSN;
ALTER INDEX WRH$_EVENT_HISTOGRAM_PK REBUILD PARTITION WRH$_EVENT__1357933872_0;

  4. 验证空间回收

    SELECT SUM(BYTES) / 1024 / 1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'WRH$_EVENT_HISTOGRAM';
SELECT SUM(BYTES) / 1024 / 1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'WRH$_EVENT_HISTOGRAM_PK';

其他注意事项

修改统计信息的保持时间

如果 SYSAUX 表空间使用率仍然很高，可以考虑修改统计信息的保持时间：

  1. 查询当前的统计信息保持时间

    SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

  2. 修改统计信息的保持时间

    EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  -- 设置为 7 天

  3. 清理 AWR 历史数据

    EXEC DBMS_STATS.PURGE_STATS(sysdate - 10);
-- 清除超过 10 天的历史统计数据，这有助于减少 SYSAUX 表空间中的数据量。

修改 AWR 收集级别

不同的 AWR 收集级别对 SYSAUX 表空间的使用率影响很大。可以通过以下参数控制 AWR 收集级别：

  1. 查询当前的 AWR 收集级别

    SHOW PARAMETER statistics_level

  2. 修改 AWR 收集级别

    ALTER SYSTEM SET statistics_level = TYPICAL SCOPE=SPFILE;

    建议设置为 TYPICAL，因为 ALL 会收集更多的数据，占用更多空间。

总结

通过上述步骤，可以有效地清理和回收 Oracle 数据库中 SYSTEM 和 SYSAUX 表空间的空间。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/480005.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

继续完善wsl相关内容：基础指令

继续完善wsl相关内容：基础指令

文章目录 前言一、我们需要安装wsl，这也是安装docker desktop的前提，因此我们在这篇文章里做了介绍：二、虽然我们在以安装docker desktop为目的时，不需要安装wsl的分发（distribution），但是装一个分发也是有诸多好处的：三、在使用wsl时，不建议把东西直接放到系统里，因…
阅读更多...
GITEX GLOBAL聚焦AI创新，Soul App创始人张璐团队带来多模态社交新体验

GITEX GLOBAL聚焦AI创新，Soul App创始人张璐团队带来多模态社交新体验

2024年10月,全球瞩目的科技盛会GITEX GLOBAL在迪拜举行。作为全球三大IT展之一,GITEX GLOBAL一直是展示全球尖端科技的重要平台,今年吸引了全球超6700家科技企业及创新公司参展。 在此次展会中,Soul App创始人张璐团队携自主研发的多模态大模型首次在国际大型展会亮相,展示了其…
阅读更多...
C++——map相关的oj题

C++——map相关的oj题

前言&#xff1a;菜鸟写博客给自己看&#xff0c;我是菜鸟。 1&#xff1a;随机链表的复制 1.1题目要求&#xff1a; 1.2解题思路&#xff1a; 可以分两步来实现代码&#xff1a; ①先将示例1链表中的val值以及next的指向关系深拷贝到另一个新的链表当中 ②再处理新链表中&am…
阅读更多...
go web单体项目 学习总结

go web单体项目 学习总结

为什么学习go 博主的主语言是Java&#xff0c;目前的工作也是做Java web开发&#xff0c;有了Java的经验后就想着再学一门语言&#xff0c;其实有两个原因&#xff0c;第一是基于兴趣&#xff0c;也想和Java对比下到底有什么不同&#xff0c;在学习go的时候让我更加了解了Java…
阅读更多...
paimon的四种changelog模式（2）-none模式

paimon的四种changelog模式（2）-none模式

# 请先了解input模式 环境创建 CREATE CATALOG fs_catalog WITH (typepaimon,warehousefile:/data/soft/paimon/catalog );USE CATALOG fs_catalog;drop table if exists t_changelog_none ;CREATE TABLE t_changelog_none (age BIGINT,money BIGINT,hh STRING,PRIMARY KEY (h…
阅读更多...
新型大语言模型的预训练与后训练范式，阿里Qwen

新型大语言模型的预训练与后训练范式，阿里Qwen

前言&#xff1a;大型语言模型&#xff08;LLMs&#xff09;的发展历程可以说是非常长&#xff0c;从早期的GPT模型一路走到了今天这些复杂的、公开权重的大型语言模型。最初&#xff0c;LLM的训练过程只关注预训练&#xff0c;但后来逐步扩展到了包括预训练和后训练在内的完整…
阅读更多...
NAT：连接私有与公共网络的关键技术（4/10）

NAT：连接私有与公共网络的关键技术（4/10）

一、NAT 的工作原理 NAT 技术的核心功能是将私有 IP 地址转换为公有 IP 地址&#xff0c;使得内部网络中的设备能够与外部互联网通信。其工作原理主要包括私有 IP 地址到公有 IP 地址的转换、端口号映射以及会话表维护这几个步骤。 私有 IP 地址到公有 IP 地址的转换&#xff1…
阅读更多...
notepad++文件github下载

notepad++文件github下载

1、github下载网址&#xff1a;Releases notepad-plus-plus/notepad-plus-plus GitHub 2、找到操作系统支持的软件&#xff1a; 3、CSDN下载链接&#xff1a;https://download.csdn.net/download/u013083576/90046203
阅读更多...
【AI绘画】Midjourney进阶：色调详解（下）

【AI绘画】Midjourney进阶：色调详解（下）

博客主页&#xff1a; [小ᶻ☡꙳ᵃⁱᵍᶜ꙳] 本文专栏: AI绘画 | Midjourney 文章目录 &#x1f4af;前言&#x1f4af;Midjourney中的色彩控制为什么要控制色彩&#xff1f;为什么要在Midjourney中控制色彩&#xff1f; &#x1f4af;色调纯色调灰色调暗色调 &#x1f4af…
阅读更多...
【MySQL篇】持久化和非持久化统计信息的深度剖析（第一篇，总共六篇）

【MySQL篇】持久化和非持久化统计信息的深度剖析（第一篇，总共六篇）

&#x1f4ab;《博主介绍》&#xff1a;✨又是一天没白过&#xff0c;我是奈斯&#xff0c;DBA一名✨ &#x1f4ab;《擅长领域》&#xff1a;✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux&#xff0c;也在扩展大数据方向的知识面✌️…
阅读更多...
PH热榜 | 2024-11-27

PH热榜 | 2024-11-27

DevNow 是一个精简的开源技术博客项目模版&#xff0c;支持 Vercel 一键部署&#xff0c;支持评论、搜索等功能&#xff0c;欢迎大家体验。 在线预览 1. Agentplace 标语&#xff1a;这是一个能创建互动式AI网站和应用的平台。 介绍&#xff1a;Agentplace是一个平台&#xf…
阅读更多...
ffmpeg 增亮 docker 使用

ffmpeg 增亮 docker 使用

使用最新的 docker pull jrottenberg/ffmpeg docker run -it --rm -v /path/to/input:/input -v /path/to/output:/output jrottenberg/ffmpeg <ffmpeg command>比如我想增亮 在 /home 目录下 有一个 video.mp4 docker run --rm -v /home:/home jrottenberg/ffmpeg:7…
阅读更多...
单片机学习笔记 11. 外部中断

单片机学习笔记 11. 外部中断

更多单片机学习笔记&#xff1a;单片机学习笔记 1. 点亮一个LED灯单片机学习笔记 2. LED灯闪烁单片机学习笔记 3. LED灯流水灯单片机学习笔记 4. 蜂鸣器滴~滴~滴~单片机学习笔记 5. 数码管静态显示单片机学习笔记 6. 数码管动态显示单片机学习笔记 7. 独立键盘单片机学习笔记 8…
阅读更多...
【PyTorch】（基础一）----pytorch环境搭建

【PyTorch】（基础一）----pytorch环境搭建

PyTorch环境搭建 该系列笔记主要参考了小土堆的视频教程&#xff0c;传送门&#xff1a;P1. PyTorch环境的配置及安装&#xff08;Configuration and Installation of PyTorch)【PyTorch教程】_哔哩哔哩_bilibili PyTorch 是一个开源的机器学习库&#xff0c;主要用 Python 编…
阅读更多...
uniapp开发支付宝小程序自定义tabbar样式异常

uniapp开发支付宝小程序自定义tabbar样式异常

解决方案&#xff1a; 这个问题应该是支付宝基础库的问题&#xff0c;除了依赖于官方更新之外&#xff0c;开发者可以利用《自定义 tabBar》曲线救国 也就是创建一个空内容的自定义tabBar&#xff0c;这样即使 tabBar 被渲染出来&#xff0c;但从视觉上也不会有问题 1.官方文…
阅读更多...
YOLOv11融合PIDNet中的PagFM模块及相关改进思路

YOLOv11融合PIDNet中的PagFM模块及相关改进思路

YOLOv11v10v8使用教程&#xff1a; YOLOv11入门到入土使用教程 YOLOv11改进汇总贴&#xff1a;YOLOv11及自研模型更新汇总 《PIDNet: A Real-time Semantic Segmentation Network Inspired by PID Controllers》 一、 模块介绍 论文链接&#xff1a;https://arxiv.org/pdf/2…
阅读更多...
NSCTF 做题笔记

NSCTF 做题笔记

[GWCTF 2019]pyre 下载附件&#xff0c;是一个pyc文件。 转换为py文件。 在用vscode打开。 分析源码。源码就是进行了异或和数值转换。 有一点很坑&#xff0c;凑得中的值要转换为ASCII值否则就是一串乱码。 编写脚本&#xff1a; #include<iostream> #include<s…
阅读更多...
java——spring容器启动流程

java——spring容器启动流程

Spring容器的启动流程是一个复杂但有序的过程&#xff0c;它涉及多个步骤来确保应用程序的组件被正确加载、配置和初始化。以下是Spring容器启动的主要步骤&#xff1a; 一、加载配置文件 Spring容器首先会加载配置文件&#xff0c;这些配置文件通常包含了应用程序的组件、依…
阅读更多...
九、Ubuntu Linux操作系统

九、Ubuntu Linux操作系统

一、Ubuntu简介 Ubuntu Linux是由南非人马克沙特尔沃思(Mark Shutteworth)创办的基于Debian Linux的操作系统&#xff0c;于2004年10月公布Ubuntu是一个以桌面应用为主的Linux发行版操作系统Ubuntu拥有庞大的社区力量&#xff0c;用户可以方便地从社区获得帮助其官方网站:http…
阅读更多...
python excel接口自动化测试框架！

python excel接口自动化测试框架！

今天采用Excel继续写一个接口自动化测试框架。 设计流程图 这张图是我的excel接口测试框架的一些设计思路。 首先读取excel文件&#xff0c;得到测试信息&#xff0c;然后通过封装的requests方法&#xff0c;用unittest进行测试。 其中&#xff0c;接口关联的参数通过正则进…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023