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

🕗 发布于 2024-11-29 16:27 oracle 数据库

背景介绍

在 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$`

查询审计数据

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

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

截断审计表
```
TRUNCATE TABLE AUD$;
```

验证空间回收

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

查看审计功能
```
SQL> show parameter audit
```
关闭审计功能
```
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 数据

查询 AWR 快照保留时间

SELECT DBMS_WORKLOAD_REPOSITORY.GET_RETENTION FROM DUAL;

修改 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);

删除过期的 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` 表的空间

查询 WRH$_ACTIVE_SESSION_HISTORY 表的分区信息

SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 / 1024 GB
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'WRH$_ACTIVE_SESSION_HISTORY';

移动 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;

重建 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;

验证空间回收

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` 表的空间

查询 WRH$_EVENT_HISTOGRAM 表的分区信息

SELECT SEGMENT_NAME, PARTITION_NAME, BYTES / 1024 / 1024 / 1024 GB
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'WRH$_EVENT_HISTOGRAM';

移动 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;

重建 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;

验证空间回收

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 表空间使用率仍然很高，可以考虑修改统计信息的保持时间：

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

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

修改统计信息的保持时间

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

清理 AWR 历史数据

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

修改 AWR 收集级别

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

查询当前的 AWR 收集级别
```
SHOW PARAMETER statistics_level
```
修改 AWR 收集级别
```
ALTER SYSTEM SET statistics_level = TYPICAL SCOPE=SPFILE;
```
建议设置为 TYPICAL，因为 ALL 会收集更多的数据，占用更多空间。

总结

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

原文地址：https://blog.csdn.net/u010674953/article/details/144080813

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：HOW - 设计一个高度封装性的组件（Modal 弹窗）
下一篇：量化交易系统开发-实时行情自动化交易-4.5.机器学习策略

传输控制协议（TCP）
在慢启动阶段，每收到1个确认段，拥塞窗口增加1个MSS。（5）当客户收到服务器发送的FIN段后，向服务器发送ACK段（ACK=1，seq=u+1，ack_seq=w+1），其状态由 FIN_WAIT_
阅读更多2024-11-30
大米中的虫子检测-检测储藏的大米中是否有虫子支持YOLO，VOC，COCO格式标注，4070张图片的数据集
大米中的虫子检测-检测储藏的大米中是否有虫子支持YOLO，VOC，COCO格式标注，4070张图片的数据集数据集分割4070总图像数训练组3551图片有效集362图片测试集157图片预处理自动定向：
阅读更多2024-11-30
新增工作台模块，任务中心支持一键重跑，MeterSphere开源持续测试工具v3.5版本发布
让软件测试工作更简单、更高效。
阅读更多2024-11-30
Ubuntu环境中RocketMQ安装教程
启动：cd /home/ec2-user/k8s/rocketmq/rocketmq-all-5.3.1-bin-release/bin && nohup ./mqnamesrv
阅读更多2024-11-30
计算机网络八股整理（四）
计算机网络相关八股
阅读更多2024-11-30
EfficientQAT: 大型语言模型的高效量化感知训练
然后，您可以加载 Block-AP 的量化模型以进行进一步的 E2E-QP。具体而言，E2E-QP 可以通过更改训练数据集适应不同的场景。我们在下面提供了关于 Llama-2-7B 的 w2g64 量
阅读更多2024-11-30
ASP.NET Core项目中使用SqlSugar连接多个数据库的方式
ASP.NET Core项目中使用SqlSugar连接多个数据库的方式
阅读更多2024-11-30
Linux系统之who命令的基本使用
Linux系统之who命令的基本使用
阅读更多2024-11-30
Spring Data
例如，如果增加一个查询条件，就不需要增加方法参数，只需要为参数对象增加属性即可；创建一个对象并设置好属性值，作为删除的条件，符合条件的数据都将被删除。对于分页来说，除了要查询结果以外，还需要查询总数，
阅读更多2024-11-30
OpenSSH-9.9p1 OpenSSL-3.4.0 升级步骤详细
收到漏洞扫描通知OpenSSH 安全漏洞(CVE-2023-38408)OpenSSH 安全漏洞(CVE-2023-51385)OpenSSH 安全漏洞(CVE-2023-51384)OpenSSH
阅读更多2024-11-30

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

背景介绍

当前表空间使用情况

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

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

清理 SYSTEM 表空间

清理审计表 AUD$

审计表转移至新表空间

1. 创建新表空间

2. 移动 AUD$ 表及其索引

2.1 移动 AUD$ 表

2.2 移动相关索引

3. 验证移动结果

清理 SYSAUX 表空间

查询 SYSAUX 表空间的占用情况

清理 AWR 数据

回收 WRH$_ACTIVE_SESSION_HISTORY 表的空间

回收 WRH$_EVENT_HISTOGRAM 表的空间

其他注意事项

修改统计信息的保持时间

修改 AWR 收集级别

总结

相关文章

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

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

清理 `SYSTEM` 表空间

清理审计表 `AUD$`

2. 移动 `AUD$` 表及其索引

2.1 移动 `AUD$` 表

清理 `SYSAUX` 表空间

查询 `SYSAUX` 表空间的占用情况

回收 `WRH$_ACTIVE_SESSION_HISTORY` 表的空间

回收 `WRH$_EVENT_HISTOGRAM` 表的空间