想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。
加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。
同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。
如果你有想了解的知识点希望我们发文可以后台私信。
最近联合几个 Oracle ACE技术专家 开通了一个付费微信群，都是具有10多年金融、医疗、制造业10年以上的一线专家，坑位费399/人，无限期，目前群内近135人。加群后会有一些福利（分享各类技术文档，干货资源，问题解答等等），更有特邀嘉宾会定期在群内直播，解读AWR，快问快答等！有问题我们尽量都解答，毕竟399不能都解决所有问题。有兴趣联系微：ywu0613
内部知识库正在筹建中,不止有oracle!
正文开始
在 Oracle 数据库的运维工作中，Undo 管理是一个至关重要的环节。Undo 数据不仅关系到数据库的事务回滚能力，还直接影响到数据一致性和性能表现。本文将带你深入了解 Undo 管理的关键知识点，包括如何检查和调整 Undo 表空间的自动扩展、属性设置、大小优化，以及如何监控 Undo 的使用情况。通过实际案例和 SQL 示例，帮助你更好地掌握 Undo 管理技巧，提升数据库运维效率。
检查 Undo 表空间是否自动扩展
查询 Undo 表空间文件属性
要确保数据库的 Undo 表空间能够根据需要自动扩展，首先需要检查其文件属性。通过以下 SQL 查询，可以查看 Undo 表空间的数据文件是否启用了自动扩展功能，以及当前的使用情况和最大容量。
SET linesize 1000 pagesize 1000
COL FILE_NAME FOR A50
COL TABLESPACE_NAME FOR A50
COL AUTOEXTENSIBLE FOR A15
COL STATUS FOR A10
COL MAXBYTES FOR A15
SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, STATUS,
MAXBYTES 1024 / 1024 / 1024 MAX_GB,
USER_BYTES 1024 / 1024 / 1024 USER_GB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE '%UNDO%';复制
查询结果解读
FILE_NAME：Undo 表空间的数据文件路径。 TABLESPACE_NAME：表空间名称，通常以 UNDOTBS
开头。AUTOEXTENSIBLE：是否启用自动扩展， YES
表示启用，NO
表示未启用。STATUS：文件状态，通常为 ONLINE
。MAX_GB：文件的最大容量（以 GB 为单位）。 USER_GB：当前已使用的容量（以 GB 为单位）。
实际案例
假设查询结果显示 AUTOEXTENSIBLE
为 NO
，且 USER_GB
接近 MAX_GB
，这表明 Undo 表空间可能无法满足未来的扩展需求。此时，建议启用自动扩展功能或增加表空间的最大容量。
查看 Undo 表空间属性
查询 Undo 表空间属性
通过以下 SQL 查询，可以查看 Undo 表空间的属性，包括自动扩展设置和数据保留时间（Retention）。
SELECT B.TABLESPACE_NAME, AUTOEXTENSIBLE, RETENTION
FROM DBA_TABLESPACES A, DBA_DATA_FILES B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND B.TABLESPACE_NAME LIKE 'UNDOTBS%';复制
查询结果解读
TABLESPACE_NAME：表空间名称。 AUTOEXTENSIBLE：是否启用自动扩展。 RETENTION：数据保留时间（以秒为单位）。
实际案例
如果查询结果显示 RETENTION
时间较短（如 3600 秒，即 1 小时），可能需要根据业务需求调整保留时间，以确保足够的 Undo 数据保留。
调整 Undo 时长或大小
查询 Undo 大小和保留时间
在调整 Undo 表空间的大小或保留时间之前，需要了解当前的 Undo 使用情况和建议的优化值。以下 SQL 查询可以帮助你获取这些信息。
查询实际 Undo 大小和建议的保留时间
SET LINE 1000 PAGESIZE 1000
COL UNDO_RETENTION[Sec] FOR A30
SELECT D.UNDO_SIZE (1024 * 1024 * 1024) "ACTUAL_UNDO_SIZE[GB]",
SUBSTR(E.VALUE, 1, 25) "UNDO_RETENTION[Sec]",
ROUND((D.UNDO_SIZE (TO_NUMBER(F.VALUE) * G.UNDO_BLOCK_PER_SEC))) "OPTIMAL_UNDO_RETENTION[Sec]"
FROM (
SELECTSUM(A.BYTES) UNDO_SIZE
FROM V$DATAFILE A, V$TABLESPACE B, DBA_TABLESPACES C
WHERE C.CONTENTS = 'UNDO'
AND C.STATUS = 'ONLINE'
AND B.NAME = C.TABLESPACE_NAME
AND A.TS# = B.TS#
) D,
V$PARAMETER E,
V$PARAMETER F,
(
SELECTMAX(UNDOBLKS ((END_TIME - BEGIN_TIME) * 3600 * 24)) UNDO_BLOCK_PER_SEC
FROM V$UNDOSTAT
) G
WHERE E.NAME = 'UNDO_RETENTION'
AND F.NAME = 'DB_BLOCK_SIZE';复制
**ACTUAL_UNDO_SIZE[GB]**：当前 Undo 表空间的实际大小（以 GB 为单位）。 **UNDO_RETENTION[Sec]**：当前的 Undo 保留时间（以秒为单位）。 **OPTIMAL_UNDO_RETENTION[Sec]**：建议的 Undo 保留时间（以秒为单位）。
调整 Undo 保留时间
如果磁盘空间紧张，可以通过调整 UNDO_RETENTION
参数来减少 Undo 数据的保留时间。以下示例将保留时间设置为 3 小时（10800 秒）。
ALTER SYSTEM SET UNDO_RETENTION = 10800 SCOPE = BOTH;复制
调整 Undo 表空间大小
如果磁盘空间充足，可以增加 Undo 表空间的大小，以确保足够的 Undo 数据存储。通过以下 SQL 查询，可以计算所需的 Undo 表空间大小。
查询所需 Undo 表空间大小
SET LINE 1000 PAGESIZE 1000
COL UNDO_RETENTION FOR A50
COL DB_BLOCK_SIZE FOR A50
SELECT UR UNDO_RETENTION,
DBS DB_BLOCK_SIZE,
((UR * (UPS * DBS)) + (DBS * 24)) 1024 / 1024AS"USED(MB)"
FROM (
SELECTVALUEAS UR
FROM V$PARAMETER
WHERENAME = 'UNDO_RETENTION'
),
(
SELECT (SUM(UNDOBLKS) SUM((END_TIME - BEGIN_TIME) * 86400)) UPS
FROM V$UNDOSTAT
),
(
SELECTVALUEAS DBS
FROM V$PARAMETER
WHERENAME = 'DB_BLOCK_SIZE'
);复制
查询结果解读
UNDO_RETENTION：Undo 保留时间（以秒为单位）。 DB_BLOCK_SIZE：数据库块大小（以字节为单位）。 **USED(MB)**：所需的 Undo 表空间大小（以 MB 为单位）。
实际案例
假设查询结果显示 USED(MB)
为 1024 MB（即 1 GB），而当前 Undo 表空间大小为 512 MB，建议增加 Undo 表空间的大小，以满足业务需求。
查看 Undo 中 SQL 语句的占用情况
查询 Undo 使用情况
通过以下 SQL 查询，可以查看 Undo 中 SQL 语句的占用情况，了解哪些查询消耗了最多的 Undo 空间。
SELECT *
FROM (
SELECT MAXQUERYID,
ROUND(SUM(UNDOBLKS) * 8 / 1024) CONSUMED_SIZE_MB
FROM V$UNDOSTAT
GROUPBY MAXQUERYID
ORDERBY CONSUMED_SIZE_MB DESC
)
WHEREROWNUM < 50;复制
查询结果解读
MAXQUERYID：SQL 语句的唯一标识符。 CONSUMED_SIZE_MB：该 SQL 语句占用的 Undo 空间（以 MB 为单位）。
实际案例
如果发现某个 SQL 语句占用了大量的 Undo 空间，可以考虑优化该 SQL 语句，减少其对 Undo 的消耗。
查看当前会话/用户使用 Undo 的情况
查询当前会话的 Undo 使用情况
通过以下 SQL 查询，可以查看当前哪些会话正在使用 Undo，以及它们的使用情况。
sql复制
SELECT A.SID, A.SERIAL#, A.USERNAME, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR = B.SES_ADDR;复制
查询结果解读
SID：会话 ID。 **SERIAL#**：会话序列号。 USERNAME：用户名称。 USED_UREC：使用的 Undo 记录数。 USED_UBLK：使用的 Undo 块数。
实际案例
如果发现某个会话的 Undo 使用量异常高，可以进一步调查该会话的活动，必要时终止会话以释放资源。
每秒生成的 Undo 量
查询每秒生成的 Undo 量
通过以下 SQL 查询，可以计算每秒生成的 Undo 块数，了解 Undo 数据的生成速率。
SELECT (SUM(UNDOBLKS)) / SUM((END_TIME - BEGIN_TIME) * 86400)
FROM V$UNDOSTAT;复制
查询结果解读
每秒生成的 Undo 块数：该值反映了 Undo 数据的生成速率，可用于评估 Undo 表空间的需求。
实际案例
如果每秒生成的 Undo 块数较高，可能需要增加 Undo 表空间的大小或调整 UNDO_RETENTION
参数。
当前 Undo 表空间具体使用情况
查询 Undo 表空间使用情况
通过以下 SQL 查询，可以查看 Undo 表空间的使用情况，包括已使用、空闲和保留的空间。
sql复制
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;复制
查询结果解读
STATUS：Undo 空间的状态，包括 ACTIVE
（已使用）、UNUSED
（空闲）和EXPIRED
（已过期）。**SUM(BYTES)**：状态对应的字节数。 **COUNT(*)**：状态对应的段数。
实际案例
如果发现 ACTIVE
状态的 Undo 空间接近最大容量，可能需要调整 Undo 表空间的大小或优化 Undo 数据的使用。
计算过去 7*24 小时中 Undo 表空间的平均使用量
查询过去 7*24 小时的 Undo 使用量
通过以下 SQL 查询，可以计算过去 7 天 24 小时中 Undo 表空间的平均使用量，评估 Undo 表空间的需求。
SET LINESIZE 1000 PAGESIZE 1000
COL UNDO_RETENTION FOR A50
COL DB_BLOCK_SIZE FOR A50
SELECT UR UNDO_RETENTION,
DBS DB_BLOCK_SIZE,
((UR * (UPS * DBS)) + (DBS * 24)) / 1024 / 1024AS"USED(MB)"
FROM (
SELECTVALUEAS UR
FROM V$PARAMETER
WHERENAME = 'UNDO_RETENTION'
),
(
SELECT (SUM(UNDOBLKS) / SUM((END_TIME - BEGIN_TIME) * 86400)) UPS
FROM V$UNDOSTAT
),
(
SELECTVALUEAS DBS
FROM V$PARAMETER
WHERENAME = 'DB_BLOCK_SIZE'
);复制
查询结果解读
UNDO_RETENTION：Undo 保留时间（以秒为单位）。 DB_BLOCK_SIZE：数据库块大小（以字节为单位）。 **USED(MB)**：过去 7 天 24 小时中 Undo 表空间的平均使用量（以 MB 为单位）。
实际案例
如果查询结果显示平均使用量较高，建议增加 Undo 表空间的大小，以满足长期运行的需求。
按峰值情况计算 Undo 表空间容量
查询峰值情况下的 Undo 使用量
通过以下 SQL 查询，可以计算在业务峰值情况下 Undo 表空间的需求容量，确保在高负载时 Undo 表空间不会耗尽。
sql复制
SELECT UR UNDO_RETENTION,
DBS DB_BLOCK_SIZE,
((UR * (UPS * DBS)) + (DBS * 24)) / 1024 / 1024AS"M_BYTES"
FROM (
SELECTVALUEAS UR
FROM V$PARAMETER
WHERENAME = 'UNDO_RETENTION'
),
(
SELECT (UNDOBLKS / ((END_TIME - BEGIN_TIME) * 86400)) UPS
FROM V$UNDOSTAT
WHERE UNDOBLKS IN (SELECTMAX(UNDOBLKS) FROM V$UNDOSTAT)
),
(
SELECTVALUEAS DBS
FROM V$PARAMETER
WHERENAME = 'DB_BLOCK_SIZE'
);复制
查询结果解读
UNDO_RETENTION：Undo 保留时间（以秒为单位）。 DB_BLOCK_SIZE：数据库块大小（以字节为单位）。 M_BYTES：峰值情况下 Undo 表空间的需求容量（以 MB 为单位）。
实际案例
如果查询结果显示峰值需求容量较高，建议在业务高峰期前调整 Undo 表空间的大小，以避免性能问题。
计算 Undo 表空间合理容量
计算业务高峰期每秒产生 Undo 数据块的个数
通过以下 SQL 查询，可以计算业务高峰期每秒产生的 Undo 数据块数，用于评估 Undo 表空间的需求。
SELECT MAX(UNDOBLKS / ((END_TIME - BEGIN_TIME) * 24 * 3600))
FROM V$UNDOSTAT;复制
查询 Undo 保留时间和数据块大小
通过以下命令，可以查看当前的 Undo 保留时间和数据块大小。
sql复制
SHOW PARAMETER UNDO_RETENTION;
SHOW PARAMETER DB_BLOCK_SIZE;复制
计算 Undo 表空间合理容量
通过以下 SQL 查询，可以计算 Undo 表空间的合理容量，确保在业务高峰期和长期运行中都能满足需求。
SELECT (UNDOBLKS * UNDO_RETENTION * DB_BLOCK_SIZE) / 1024 / 1024 / 1024 UNDOTABLESPACE_GB
FROM DUAL;复制
查询结果解读
UNDOTABLESPACE_GB：Undo 表空间的合理容量（以 GB 为单位）。
实际案例
如果计算结果显示 Undo 表空间的合理容量为 5 GB，而当前容量仅为 2 GB，建议增加 Undo 表空间的大小，以满足业务需求。
Undo 管理是 Oracle 数据库运维中的一个重要环节。通过合理配置 Undo 表空间的自动扩展、调整 Undo 保留时间和大小，以及监控 Undo 的使用情况，可以有效提升数据库的性能和稳定性。在实际工作中，建议根据业务需求和数据库的运行情况，定期检查和优化 Undo 表空间，确保数据库的高效运行。
如果你觉得这篇文章对你有帮助，欢迎点赞、收藏和分享。如果你有任何关于 Undo 管理或其他数据库相关的问题，欢迎在评论区留言讨论。
往期文章回顾
MOP社区新闻
金仓专栏
告别繁琐！KingbaseES v9数据库一键安装-青学会&金仓专栏（1）
KingbaseES v9数据库Docker安装-青学会&金仓专栏（2）
DBA实战小技巧
实战：记一次RAC故障排查
DBA实战运维小技巧安装篇（一）Oracle 主流版本不同架构下的静默安装指南
DBA实战运维小技巧存储篇（一）根目录满了如何处理
DBA实战运维小技巧存储篇（二）打包迁移单机数据库至新存储
MOP社区投稿-内核开发
简单解析 IvorySQL 增强 Oracle xml 兼容能力的原理
简单讨论 PostgreSQL C语言拓展函数返回数据表的方式
简单分析 pg_config 程序的作用与原理
Redis 日志机制简介（一）：SlowLog
Redis 日志机制简介（二）：AOF 日志
Redis 日志机制简介（三）：RDB 日志
pg_cron插件使用介绍
Redis 的指令表实现机制简介
pg几款源码工具介绍
Redis 事务功能简介
