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) 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 表空间中较大的表

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

SELECT * FROM (
  SELECT SEGMENT_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 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 代码块来创建它。

DECLARE
  v_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_dir
    FROM v$datafile
    WHERE 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.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_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$';

-- 移动每个索引
BEGIN
  FOR i IN (SELECT index_name FROM dba_indexes WHERE table_owner = 'SYS' AND table_name = 'AUD$') LOOP
    EXECUTE 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_OCCUPANTS
 ORDER 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 GB
  FROM DBA_SEGMENTS
 WHERE 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 GB
  FROM DBA_SEGMENTS
 WHERE 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 表空间的空间。

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Android PMS（Package Manager Service）源码介绍

Android PMS（Package Manager Service）源码介绍

文章目录 前言一、PMS 启动流程二、APK 安装流程三、APK 卸载流程四、权限管理静态权限动态权限 五、 数据存储与一致性六、 PMS 的安全性策略1、权限检查2、签名认证3、动态权限管理4、应用安装验证5、保护系统目录 七、PMS 调试方法总结 前言 PackageManagerService&#xf…
阅读更多...
OSPTrack：一个包含多个生态系统中软件包执行时生成的静态和动态特征的标记数据集，用于识别开源软件中的恶意行为。

OSPTrack：一个包含多个生态系统中软件包执行时生成的静态和动态特征的标记数据集，用于识别开源软件中的恶意行为。

2024-11-22 &#xff0c;由格拉斯哥大学创建的OSPTrack数据集&#xff0c;目的是通过捕获在隔离环境中执行包和库时生成的特征&#xff0c;包括静态和动态特征&#xff0c;来识别开源软件&#xff08;OSS&#xff09;中的恶意指标&#xff0c;特别是在源代码访问受限时&#xf…
阅读更多...
Web登录页面设计

Web登录页面设计

记录第一个前端界面&#xff0c;暑假期间写的&#xff0c;用了Lottie动画和canvas标签做动画&#xff0c;登录和注册也连接了数据库。 图片是从网上找的&#xff0c;如有侵权私信我删除&#xff0c;谢谢啦~
阅读更多...
MySQL45讲 第29讲 如何判断一个数据库是不是出问题了？——阅读总结

MySQL45讲 第29讲 如何判断一个数据库是不是出问题了？——阅读总结

文章目录 MySQL45讲 第二十九讲 如何判断一个数据库是不是出问题了&#xff1f;——阅读总结一、检测数据库实例健康状态的重要性二、常见检测方法及问题分析&#xff08;一&#xff09;select 1 判断法&#xff08;二&#xff09;查表判断法&#xff08;三&#xff09;更新判断…
阅读更多...
mac下Gpt Chrome升级成GptBrowser书签和保存的密码恢复

mac下Gpt Chrome升级成GptBrowser书签和保存的密码恢复

cd /Users/自己的用户名/Library/Application\ Support/ 目录下有 GPT\ Chrome/ Google/ GptBrowser/ GPT\ Chrome 为原来的chrome浏览器的文件存储目录. GptBrowser 为升级后chrome浏览器存储目录 书签所在的文件 Bookmarks 登录账号Login 相关的文件 拷贝到GptBrow…
阅读更多...
论文阅读笔记 | EEG：运动执行过程中的ERD

论文阅读笔记 | EEG：运动执行过程中的ERD

参考&#xff1a;https://mp.weixin.qq.com/s/RmcPSLv1ITMZZwqe2uZ_og?token1093147649&langzh_CN
阅读更多...
Android U ART young cc流程分析

Android U ART young cc流程分析

概述&#xff1a; 众所周知jvm虚拟机为了提高内存回收效率&#xff0c;更高效的进行内存管理与回收&#xff0c;对堆内存进行了分代管理比如hotspot虚拟机的新生代&#xff0c;老年代。根据各代的特征&#xff08; 新生代对象分配频繁而生存周期短&#xff0c;老年代生存周期长…
阅读更多...
C++ 11重点总结1

C++ 11重点总结1

智能指针 智能指针: C11引入了四种智能指针: auto_ptr(已弃用)、unique_ptr、shared_ptr和weak_ptr。智能指针可以更有效地管理堆内存,并避免常见的内存泄漏问题。 shared_ptr: 自定义删除器。 shared_ptr使用引用计数来管理它指向的对象的生命周期。多个shared_ptr实例可以指向…
阅读更多...
Sickos1.1 详细靶机思路 实操笔记

Sickos1.1 详细靶机思路 实操笔记

Sickos1.1 详细靶机思路 实操笔记 免责声明 本博客提供的所有信息仅供学习和研究目的&#xff0c;旨在提高读者的网络安全意识和技术能力。请在合法合规的前提下使用本文中提供的任何技术、方法或工具。如果您选择使用本博客中的任何信息进行非法活动&#xff0c;您将独自承担…
阅读更多...
GB28181系列二：SIP信令

GB28181系列二：SIP信令

我的音视频/流媒体开源项目(github) GB28181系列目录 目录 一、SIP报文介绍 二、SIP交互流程&#xff1a; 1、Session Model 2、Pager Model 3、SIP信令交互过程中的3个定义 三、媒体传输&#xff08;SDP和RTP&#xff09; 一、SIP报文介绍 这里将会介绍SIP…
阅读更多...
【接口自动化测试】一文从0到1详解接口测试协议！

【接口自动化测试】一文从0到1详解接口测试协议！

接口自动化测试是软件开发过程中重要的环节之一。通过对接口进行测试&#xff0c;可以验证接口的功能和性能&#xff0c;确保系统正常运行。本文将从零开始详细介绍接口测试的协议和规范。 定义接口测试协议 接口测试协议是指用于描述接口测试的规范和约定。它包含了接口的请求…
阅读更多...
CentOS7执行yum命令报错，已加载插件：fastestmirrorLoading mirror speeds from cached hostfile

CentOS7执行yum命令报错，已加载插件：fastestmirrorLoading mirror speeds from cached hostfile

一、出现一下异常问题&#xff0c;表示域名没有配置或配置错误 问题一&#xff1a; 0curl: (6) Could not resolve host: mirrors.aliyun.com; 未知的错误 问题二&#xff1a;虚拟机使用ping主机&#xff0c;提示network unreachable 2.原因分析 出现这个问题是因为yum在安装…
阅读更多...
【Threejs进阶教程-着色器篇】9.顶点着色器入门

【Threejs进阶教程-着色器篇】9.顶点着色器入门

【Threejs进阶教程-着色器篇】9.顶点着色器入门 本系列教程第一篇地址&#xff0c;建议按顺序学习认识顶点着色器varying介绍顶点着色器与片元着色器分别的作用Threejs在Shader中的内置变量各种矩阵gl_Position 尝试使用顶点着色器增加分段数增强效果 制作平面鼓包效果鼓包效果…
阅读更多...
Ubuntu 硬盘分区并挂载

Ubuntu 硬盘分区并挂载

一、什么是挂载 1.挂载的定义 在 Ubuntu&#xff08;或其他 Linux 系统&#xff09;中&#xff0c;挂载&#xff08;Mount&#xff09; 是将一个存储设备或分区连接到系统的文件系统层次结构中的过程。挂载后&#xff0c;你可以通过某个目录&#xff08;挂载点&#xff09;访问…
阅读更多...
【前端开发】一文带你快速入门 JavaScript（上）Web 前端必备程序语言 | 环境搭建与基础知识

【前端开发】一文带你快速入门 JavaScript（上）Web 前端必备程序语言 | 环境搭建与基础知识

&#x1f4af; 欢迎光临清流君的博客小天地&#xff0c;这里是我分享技术与心得的温馨角落 &#x1f4af; &#x1f525; 个人主页:【清流君】&#x1f525; &#x1f4da; 系列专栏: 运动控制 | 决策规划 | 机器人数值优化 &#x1f4da; &#x1f31f;始终保持好奇心&…
阅读更多...
视频推拉流EasyDSS互联网直播点播平台技术特点及应用场景剖析

视频推拉流EasyDSS互联网直播点播平台技术特点及应用场景剖析

在数字科技日新月异的今天&#xff0c;视频直播和点播已经成为互联网内容传播的重要方式之一。而互联网直播点播平台EasyDSS作为功能强大的流媒体直播点播视频能力平台&#xff0c;提供了一站式的视频推拉流、转码、直播、点播、时移回放、存储等视频服务&#xff0c;广泛应用于…
阅读更多...
Qt读写Usb设备的数据

Qt读写Usb设备的数据

Qt读写Usb设备的数据 问题:要读取usb设备进行通讯&#xff0c;qt好像没有对应的库支持。解决&#xff1a;libusbwindow下载 :Linux下载: QtUsb 开源的第三方库库里面的函数说明&#xff1a;window版本&#xff1a;Linux中也提供的直接下载测试代码&#xff1a;库下载&#xff1…
阅读更多...
2024最新python使用yt-dlp

2024最新python使用yt-dlp

2024最新python使用yt-dlp下载YT视频 1.获取yt的cookie1&#xff09;google浏览器下载Get cookies.txt LOCALLY插件2&#xff09;导出cookie 2.yt-dlp下载[yt-dlp的GitHub地址](https://github.com/yt-dlp/yt-dlp?tabreadme-ov-file)1&#xff09;使用Pycharm(2024.3)进行代码…
阅读更多...
一加ACE 3 Pro手机无法连接电脑传输文件问题

一加ACE 3 Pro手机无法连接电脑传输文件问题

先说结论&#xff1a;OnePlus手机无法连接电脑传输数据的原因&#xff0c;大概率是一加数据线的问题。尝试其他手机品牌的数据线&#xff08;比如华为&#xff09;&#xff0c;再次尝试。 连接电脑方法&#xff1a; 1 打开开发者模式&#xff08;非必要操作&#xff09; 进入…
阅读更多...
java：aqs实现自定义锁

java：aqs实现自定义锁

aqs采用模板方式设计模式&#xff0c;需要重写方法 package com.company.aqs;import java.util.concurrent.TimeUnit; import java.util.concurrent.locks.AbstractQueuedSynchronizer; import java.util.concurrent.locks.Condition; import java.util.concurrent.locks.Lock;…
阅读更多...
最新文章

Copyright @ 2022~2023