Oracle undo段offline后undo表空间不回收的原因与解决

本核心逻辑：先定位问题（extent 状态 / 配置）→ 再修复配置（禁用 Guarantee / 降低保留时间）→ 最后触发清理（SMON）/ 释放空间（切换表空间）；

生产环境重点：Retention Guarantee 仅在闪回 / 读一致性要求极高时开启，undo_retention 建议根据业务调整（无需过大）；

空间回收本质：undo 段 offline 后，仅 EXPIRED extent 可重用，需通过切换表空间才能将空间归还操作系统。

一、核心认知：undo 段 offline ≠ 空间立即回收

Undo 段 offline 仅表示不再接受新事务，但不意味着其占用的空间会被自动释放给操作系统，甚至不一定会被立即标记为可重用。Oracle 的 undo 空间管理遵循 “惰性重用” 原则，仅在需要新空间时才会覆盖过期 (EXPIRED) 的 extent，而非主动收缩已分配空间。

二、空间不回收的 6 大常见原因

1. Extent 状态仍为 ACTIVE/UNEXPIRED（最主要）

Undo 段由多个 extent 组成，即使段本身 offline，其中的 extent 可能仍处于：

• ACTIVE即未提交的Undo信息（活动）：表示事物还在活动，该值对应的Undo段的DBA_ROLLBACK_SEGS.STATUS一定是ONLINE状态，一旦没有活动的事务在使用Undo段，那么对应的Undo段就变成OFFLINE状态。ACTIVE状态的Undo区不会被覆盖。
• UNEXPIRED即提交的Undo信息（未过期）：表示事务已经提交但是还没有超过UNDO_RETENTION指定时间，该状态可以被覆盖使用。
• EXPIRED即过期的Undo信息（过期）：表示事务已经提交且超过了UNDO_RETENTION指定时间，该状态可以被覆盖使用。

show parameter undo
set lines 200 pages 50000
col name for a40
col value for a10
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 where x.indx = y.indx
   AND x.ksppinm in ('_highthreshold_undoretention'，'_undo_autotune');

undo_retention=3600
_highthreshold_undoretention=3600

NAME                                     VALUE      DESCRIB
---------------------------------------- ---------- ------------------------------------------------------------
_highthreshold_undoretention             4294967294 high threshold undo_retention in seconds #方法1:限制undoretention最大时间，就是在自动调整的结果上强制限制最大时间（试了效果不大）
_undo_autotune                           TRUE       enable auto tuning of undo_retention     #方法2：直接关闭autotune（这个更干脆）

--alter system set "_undo_autotune"=false scope=spfile sid='*';
alter system set undo_retention=3600 scope=both sid='*';
alter system set "_highthreshold_undoretention"=3600 scope=both sid='*';

2. 启用了 Retention Guarantee（保留保障）

若设置了ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;，Oracle 会强制保留UNDO_RETENTION 时间内的所有 undo，即使空间不足也不会覆盖 UNEXPIRED 的 extent，导致空间无法回收。

3. 长时间运行的查询 / 闪回操作

正在执行的长查询或闪回操作会延长 undo 保留时间，使原本应过期的 extent 保持 UNEXPIRED 状态，阻止空间回收Oracle。

4. 系统时间异常（未来时间戳）

若数据库运行期间系统时间被调整到未来，之后又调回正常时间，会导致 undo 记录的过期时间被计算为未来值，Oracle 会认为这些 undo 仍需保留，无法回收。

5. SMON 进程未完成清理

Undo 段 offline 后，后台 SMON 进程负责清理其元数据和过期 extent，但在以下情况会延迟：

• 数据库负载过高，SMON 优先级低
• 存在需要恢复的事务（如实例异常终止后）
• undo 段存在损坏或数据字典不一致Oracle Support

6. 误解 “空间回收” 的定义

Oracle 默认不会自动收缩数据文件释放空间给操作系统，即使 undo 段 offline 且所有 extent 都 EXPIRED，空间也仅在表空间内部标记为可用，等待新事务重用，而非归还磁盘空间。

三、快速诊断步骤（SQL 脚本）

1. 查看 undo 段状态：所有段应为 OFFLINE，无 PENDING OFFLINE
SELECT segment_name, status, tablespace_name FROM dba_rollback_segs WHERE tablespace_name='APPS_UNDOTS1';
SELECT segment_name, status, tablespace_name FROM dba_rollback_segs WHERE tablespace_name='APPS_UNDOTS2';

select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='APPS_UNDOTS1' group by tablespace_name, status;
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='APPS_UNDOTS1';

select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='APPS_UNDOTS2' group by tablespace_name, status;
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='APPS_UNDOTS2';

2. 分析 extent 状态分布：ACTIVE 应为 0，UNEXPIRED 应较少
SELECT status, COUNT(*), SUM(bytes)/1024/1024 MB FROM dba_undo_extents WHERE tablespace_name='APPS_UNDOTS1' GROUP BY status;
SELECT status, COUNT(*), SUM(bytes)/1024/1024 MB FROM dba_undo_extents WHERE tablespace_name='APPS_UNDOTS2' GROUP BY status;

3. 检查 Retention Guarantee：应为 NOT GUARANTEE
SELECT tablespace_name, retention FROM dba_tablespaces WHERE tablespace_name='APPS_UNDOTS1';
SELECT tablespace_name, retention FROM dba_tablespaces WHERE tablespace_name='APPS_UNDOTS2';

4. 查找长事务 / 未提交事务：无长时间运行的 ACTIVE 事务
SELECT start_time, status FROM v$transaction;

5. 检查系统时间：与操作系统时间一致
SELECT SYSDATE FROM DUAL;

6. 查看 SMON 活动：SMON 应处于正常活动状态	
SELECT program, action FROM gv$session WHERE program LIKE '%SMON%';

四、针对性解决方案

1. 处理 ACTIVE/UNEXPIRED Extent

1. 提交 / 回滚所有未完成事务（包括分布式事务）
2. 终止长时间运行的查询 / 闪回操作
3. 临时降低 UNDO_RETENTION（需谨慎）：ALTER SYSTEM SET undo_retention=300 SCOPE=BOTH; -- 从默认900秒改为5分钟

2. 禁用 Retention Guarantee

ALTER TABLESPACE APPS_UNDOTS1 RETENTION NOGUARANTEE;
ALTER TABLESPACE APPS_UNDOTS2 RETENTION NOGUARANTEE;

3. 强制 SMON 清理（需谨慎）

1. 重启数据库（最彻底，适用于非生产环境）
2. 或执行：sqlALTER SYSTEM CHECKPOINT; ALTER SYSTEM FLUSH SHARED_POOL; 触发 SMON 进行清理工作

4. 释放空间给操作系统（可选）

若需将空间归还磁盘，可：

1. 创建新的 undo 表空间并切换
2. 等待原 undo 表空间所有段 offline 后删除

5. 修复系统时间异常

1. 停止数据库
2. 同步操作系统时间
3. 启动数据库并执行完全检查点

五、总结与最佳实践

1. undo 段 offline 只是空间回收的前提，而非结果，真正决定空间可用性的是extent 状态
2. 日常维护应：
   • 避免设置过长的 UNDO_RETENTION（默认 900 秒通常足够）
   • 仅在必要时启用 Retention Guarantee
   • 监控长事务和 undo 空间使用情况
3. 若需释放空间给操作系统，切换 undo 表空间并删除旧表空间是最可靠的方法