Oracle ORA-00236错误详解与解决
ORA-00236错误全面解析
1 官方正式说明
1.1 错误概述
ORA-00236是Oracle数据库中的一个快照操作错误，官方定义为：“snapshot operation invalid: rollback segment number %s with name “%s” too small”（快照操作无效：回滚段号%s，名称"%s"太小）。
1.2 错误信息结构
- 错误代码：ORA-00236
- 错误消息：snapshot operation invalid: rollback segment number [number] with name “[name]” too small
- 参数说明：
[number]：回滚段编号[name]：回滚段名称
- 错误级别：用户会话级错误
1.3 技术原理
ORA-00236错误发生在Oracle的读一致性机制中。当执行长时间运行的查询时，Oracle需要确保查询开始时看到的数据在查询期间保持一致。为实现这一目标，Oracle使用回滚段来存储数据修改前的映像。
当查询需要访问已被修改的旧数据时，会从回滚段中读取。如果回滚段空间不足或太小，无法存储所有必要的旧数据映像，就会触发ORA-00236错误。
2 错误原因深度分析
2.1 根本原因
ORA-00236的核心原因是回滚段配置不当，无法满足长时间运行查询的读一致性需求。
2.2 具体触发条件
| 触发条件 | 描述 | 发生概率 |
|---|---|---|
| 回滚段太小 | 回滚段空间不足以存储长时间查询所需的所有旧数据映像 | 高 |
| 长时间查询+并发DML | 大查询运行期间有大量数据修改操作 | 高 |
| 回滚段自动扩展限制 | 回滚段达到MAXEXTENTS限制无法继续扩展 | 中 |
| 回滚段竞争 | 多个会话竞争使用同一回滚段资源 | 中 |
2.3 相关技术背景
-- Oracle读一致性机制示例
-- 时间点T1: 查询开始，SCN=1000
SELECT * FROM large_table; -- 需要10分钟完成
-- 时间点T2 (T1+5分钟): 其他会话更新数据
UPDATE large_table SET status = 'MODIFIED' WHERE id = 5000;
COMMIT;
-- 查询需要从回滚段获取id=5000的旧值
-- 如果回滚段已满或太小，无法保存旧值，则报ORA-00236
3 诊断与定位方法
3.1 错误发生时的诊断步骤
步骤1：检查错误详情
-- 查看错误完整信息
SELECT * FROM v$diag_info WHERE name = 'Default Trace File';
-- 检查跟踪文件获取详细错误堆栈
步骤2：分析回滚段状态
-- 检查回滚段配置和使用情况
SELECT segment_name, tablespace_name, status,
bytes/1024/1024 as size_mb, blocks, extents
FROM dba_rollback_segs
WHERE status = 'ONLINE';
-- 检查回滚段扩展信息
SELECT segment_name, max_extents, extents,
bytes/1024/1024 current_size_mb,
(SELECT sum(bytes)/1024/1024
FROM dba_segments
WHERE segment_name = rs.segment_name) as allocated_mb
FROM dba_rollback_segs rs
WHERE status = 'ONLINE';
步骤3：识别问题查询
-- 查找长时间运行的查询
SELECT sid, serial#, username, sql_id, elapsed_time, cpu_time,
sql_text, program, machine
FROM v$session_longops
WHERE time_remaining > 0;
-- 检查当前活动会话和SQL
SELECT s.sid, s.serial#, s.username, s.program,
q.sql_text, s.status, s.last_call_et
FROM v$session s, v$sql q
WHERE s.sql_address = q.address
AND s.sql_hash_value = q.hash_value
AND s.status = 'ACTIVE';
3.2 相关数据字典查询
-- 监控回滚段使用情况
SELECT r.name,
s.extents,
s.rssize/1024/1024 as size_mb,
s.xacts as active_transactions,
s.gets,
s.waits,
round(s.waits/s.gets*100,2) as wait_pct
FROM v$rollstat s, v$rollname r
WHERE s.usn = r.usn;
-- 检查回滚段扩展历史
SELECT segment_name, tablespace_name,
initial_extent, next_extent,
min_extents, max_extents
FROM dba_rollback_segs;
4 解决方案
4.1 立即应对措施
方案1：优化问题查询
-- 将大查询分解为多个小查询
-- 原始查询（可能触发ORA-00236）
-- SELECT * FROM large_table WHERE create_date >= ADD_MONTHS(SYSDATE, -12);
-- 优化为分批查询
BEGIN
FOR i IN 1..12 LOOP
INSERT INTO temp_results
SELECT * FROM large_table
WHERE create_date >= ADD_MONTHS(SYSDATE, -i)
AND create_date < ADD_MONTHS(SYSDATE, -(i-1));
COMMIT;
END LOOP;
END;
/
-- 使用物化视图或临时表
CREATE MATERIALIZED VIEW log ON large_table;
CREATE MATERIALIZED VIEW large_table_mv
REFRESH FAST ON COMMIT
AS SELECT * FROM large_table;
方案2：临时增加回滚段大小
-- 为问题回滚段增加空间
ALTER ROLLBACK SEGMENT rbs1 STORAGE (NEXT 100M);
-- 或者创建临时大回滚段
CREATE ROLLBACK SEGMENT large_rbs
TABLESPACE rbs_tbs
STORAGE (INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED);
ALTER ROLLBACK SEGMENT large_rbs ONLINE;
4.2 短期解决方案
优化数据库配置
-- 检查当前UNDO配置
SELECT name, value
FROM v$parameter
WHERE name LIKE '%undo%';
-- 优化UNDO表空间配置
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH; -- 增加保留时间
ALTER SYSTEM SET undo_management = AUTO SCOPE=SPFILE;
调整应用程序逻辑
-- 在低业务时段执行大查询
-- 使用更高效的查询方式
-- 避免在查询高峰期运行报表查询
4.3 长期根治方案
方案1：优化UNDO表空间设计
-- 创建专用的UNDO表空间
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oradata/undotbs2_01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
-- 切换UNDO表空间
ALTER SYSTEM SET undo_tablespace = undotbs2 SCOPE=BOTH;
方案2：实施最佳实践
-- 定期监控和维护
-- 设置自动扩展和警报
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '85',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '97',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'UNDOTBS1');
END;
/
5 相关联的ORA错误
5.1 相关错误对照表
| 错误代码 | 错误描述 | 关联性 |
|---|---|---|
| ORA-01555 | snapshot too old | 高度相关，都是读一致性问题 |
| ORA-30036 | unable to extend segment in undo tablespace | 空间扩展问题 |
| ORA-01650 | unable to extend rollback segment | 回滚段扩展失败 |
| ORA-01653 | unable to extend table | 表空间空间不足 |
5.2 错误链分析
ORA-00236通常不是孤立发生的，可能是一系列问题的最终表现：
- 空间压力 → ORA-30036 → ORA-00236
- 配置不当 → ORA-01555 → ORA-00236
- 性能问题 → 长时间查询 → ORA-00236
6 通俗易懂的讲解
6.1 生活化比喻
把Oracle的读一致性机制想象成一个图书馆借书系统：
- 数据块 = 书架上的书
- DML操作 = 有人借走书并修改内容
- 回滚段 = 图书馆的"修改记录本"，记录谁借了什么书，原内容是什么
- 长时间查询 = 你要统计所有书架上的书籍信息
ORA-00236错误就像：你的统计工作很慢，期间很多人借书还书。记录本太小，写不下所有的借还记录。当你想知道某本书的原始信息时，记录本上已经找不到相关记录了。
6.2 简单总结
ORA-00236的本质是：数据库的"记忆"不够用。长时间运行的查询需要记住数据开始时的样子，但回滚段这个"记忆空间"太小，记不住那么多变化。
6.3 实用建议
对于普通用户和DBA，处理ORA-00236可以遵循以下简单原则：
-
对于开发人员：
- 避免在业务高峰期运行大查询
- 把大查询拆分成小查询分批处理
- 考虑使用物化视图预先计算数据
-
对于DBA：
- 确保UNDO表空间足够大（通常为数据库大小的10-20%）
- 设置合适的undo_retention参数（根据最长查询时间设定）
- 监控长时间运行的查询并优化
-
紧急处理：
- 首先尝试优化或终止问题查询
- 临时增加UNDO表空间大小
- 在系统空闲时重试操作
7 预防措施
7.1 配置最佳实践
-- 预防性配置检查
SELECT
tablespace_name,
sum(bytes)/1024/1024 as total_mb,
status,
autoextensible
FROM dba_data_files
WHERE tablespace_name LIKE '%UNDO%'
GROUP BY tablespace_name, status, autoextensible;
-- 设置合理的undo保留时间（根据最长查询时间）
ALTER SYSTEM SET undo_retention = 10800; -- 3小时
7.2 监控脚本示例
-- 定期监控UNDO使用情况
SELECT
TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') as time,
undoblks,
txncount,
maxquerylen
FROM v$undostat
ORDER BY begin_time DESC;
8 总结
ORA-00236是Oracle数据库中与读一致性相关的配置错误，主要原因是回滚段/UNDO表空间配置无法满足长时间查询的需求。解决这一问题的关键在于：
- 优化查询性能 - 减少查询执行时间
- 合理配置UNDO - 确保足够的保留时间和空间
- 实施监控预警 - 提前发现潜在问题
- 应用程序优化 - 避免不必要的大查询
通过综合应用上述解决方案，可以有效预防和解决ORA-00236错误，确保数据库的稳定运行。
欢迎关注我的公众号《IT小Chen》
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
