ORA-01555 总结

--精彩解释

不知道是从哪里转的了, 假设有张表,叫table1,里面有5000万行数据,假设预计全表扫描1次需要1个小时,我们从过程来看: 

1、在1点钟,有个用户A发出了select * from table1;此时不管将来table1怎么变化,正确的结果应该是用户A会看到在1点钟这个时刻的内容。这个是没有疑问的。 
2、在1点30分,有个用户B执行了update命令,更新了table1表中的第4000万行的这条记录,这时,用户A的全表扫描还没有到达第4000万条。毫无疑问,这个时候,第4000万行的这条记录是被写到了回滚段里去了的,我假设是回滚段RBS1,如果用户A的全表扫描到达了第4000万行,是应该会正确的从回滚段RBS1中读取出1点钟时刻的内容的。 
3、这时,用户B将他刚才做的操作commit了,但是这时,系统仍然可以给用户A提供正确的数据,因为那第4000万行记录的内容仍然还在回滚段RBS1里,系统可以根据SCN来到回滚段里找到正确的数据,但是大家注意到,这时记录在RBS1里的第4000万行记录已经发 生了一点重大的改变:就是这个第4000万行的在回滚段RBS1里的数据有可能随时被覆盖掉,因为这条记录已经被提交了!!! 
4、由于用户A的查询时间漫长,而业务在一直不断的进行,RBS1回滚段在被多个不同的tracnsaction使用着,这个回滚段里的extent循环到了第4000万行数据所在的extent,由于这条记录已经被标记提交了,所以这个extent是可以被其他transaction覆盖掉的! 
5、到了1点40分,用户A的查询终于到了第4000万行,而这时已经出现了第4条说的情况,需要到回滚段RBS1去找数据,但是已经被覆盖掉了,于是01555就出现了。

--错误提示

数据库报错 ORA-01555 什么回滚段 '_SYSSMU168' is too small.很明显 是可用的回滚段太小了 满足不了那个大事物的需要 具体的sql我就不提供了

还有一种可能,一般伴随着ORA-22924出现就是LOB上的问题

辨别ORA-01555是不是发生在LOB上的,一般来说,普通的01555错误会指明发生01555的rollback segment,而LOB的则没有,而是伴随着ORA-22924出现
http://www.dbafan.com/blog/?p=11

辨别ORA-01555是不是发生在LOB上的,一般来说,普通的01555错误会指明发生01555的rollback segment,而LOB的则没有,而是伴随着ORA-22924出现http://www.dbafan.com/blog/?p=11

 

--回滚原理

回退段中存放的信息被称为“前照”(pre-image),也就是说当一个进程对某个表进行了DML操作以后,
更改前的纪录信息被存放于回滚段,其作用有两个:

1、当进程要求回滚(ROLLBACK)的时候,使用回滚段中信息是纪录复原;

2、保持数据读的一致性,当一个进程从某个表中读纪录的时候,ORACLE返回的是当读开始或者进程开始时的纪录,如果在读取过程中有其他进程更改了表纪录,ORACLE就会从回滚段中读取当读操作开始时的数据。回滚段中信息并不是持久有效的,当进程提交(COMMIT)或者回滚(ROLLBACK)的时候,回滚段就被释放了。当一个进程在执行一个大查询的时候,如果在查询的过程中所读取得的表被更改而且更改COMMIT太久,那回滚段中的“前照”就有可能会被其他的进程覆盖,从而导致ORA-01555错误。

 

--解决方法

1、增加回滚段的大小,因为ORACLE总是覆盖最旧的回滚段,所以大的回滚段能有效的降低数据被覆盖的可能性。
2、检查你的程序,避免在一个大查询的过程中对所查询的表执行太多更新操作。

下面回顾下关于ora-01555的解决方法 10g默认是使用AUM 这里就不说了. 下面是几个解决方式来自hellodba 总结的很不错 大家可用参考下:

1、扩大回滚段: 因为回滚段是循环使用的,如果回滚段足够大,那么那些被提交的数据信息就能保存足够长的时间是那些大事务完成一致性读取

2、增加undo_retention时间:在undo_retention规定的时间内,任何其他事务都不能覆盖这些数据。

3、优化相关查询语句,减少一致性读:减少查询语句的一致性读,就降低读取不到回滚段数据的风险。这一点非常重要!

4、减少不必要的事务提交:提交的事务越少,产生的回滚段信息就越少。

5、对大事务指定回滚段,通过以下语句可以指定事务的回滚段:SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment; 给大事务指定回滚段,即降低大事务回滚信息覆盖其他事务的回滚信息的几率,又降低了他自身的回滚信息被覆盖的几率。大事务的存在,往往是1555错误产生的诱因。

6、使用游标时尽量使用显式游标,并且只在需要的时候打开游标,同时将所有可以在游标外做的操作从游标循环中拿出。当游标打开时,查询就开始了,直到游标关闭。减少游标的打开时间,就减少了1555错误发生的几率。http://hi.baidu.com/xu521huan/blog/item/0903ec9b62d85ebec8eaf442.html

 

--一些实例

我的回答是先看看到底是哪个SQL有这个问题,再确定不是因为SQL本身太糟糕导致SNAPSHOT TOO OLD。再跟他们说我不相信把UNDO_RETENTION加大会有效地解决问题。最后给几个CASES来支持我的观点。
(1)reduce the frequency of commit
(2)set initialization paramter undo_retention(9i)
(3)alter system set retention guarrantee (10g)
(4)increase the size of the undo tablespace
(5)assign a large rollback segment for the large transaction
(6)tuning the long run sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
the root cause of the error ora-01555:the long run query can not find a consistent image, because the undo blocks that used to construct the consistent image were wrapped by other active transaction.

遇到这个问题,首先可以看是维护需要执行的SQL或者应用执行的SQL报的

1、如果平时不报,只是维护人员执行的SQL报的,一般是SQL写得不好,运行执行过长,超过了参数 redo_retention所设置的时间造成的。这种情况可以协助他们进行SQL分析和优化,减少运行时间,这个情况下系统不需要对系统进行调整

2、如果是应用程序报的,比如批量程序,则需要通知相关人员进行重做,否则批量运行失败,业务可能会因为数据遗漏出现问题。如果出现的频率较多,则需要在优化应用程序(优化的手段有SQL优化、适当增加commit的次数等)。在应用新版本上线前,可通过调整系统配置临时解决问题方法如:

1)增大undo表空间

2)增大redo_retention

3)为此大事物指定专门的undo 段

http://www.itpub.net/viewthread.php?tid=1021888&extra=&highlight=DBA%C3%E6%CA%D4&page=3

新鲜出炉的案例:APPS的人下午回馈说今天一个DB的JOB一直报SNAPSHOT TOO OLD。这是过去几个月这个数据库第一次有这种回馈。到ALERT LOG中看看,有好多这种ERROR:Wed Jul 16 10:30:44 2008 ORA-01555 caused by SQL statement below (Query Duration=884 sec, SCN: 0x0018.bef62785):Wed Jul 16 10:30:44 2008

Wed Jul 16 10:57:29 2008 ORA-01555 caused by SQL statement below (Query Duration=149 sec, SCN: 0x0018.bf0d3e47):Wed Jul 16 10:57:29 2008

嗯,884S,149S,不可能吧?看看UNDO SETTINGS,很大啊:

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

SQL> select sum(bytes)/(1024*1024*1024) as gbytes from dba_data_files;
    GBYTES
----------
300.654297

SQL> select sum(bytes)/(1024*1024*1024) as gbytes from dba_data_files where tablespace_name='UNDOTBS2';
    GBYTES
----------
  9.765625

自己试试:
create table mytab as <the select statement> where 1=0
16:12:14 SQL> insert into mytab <the select statement>
insert into mytab
            *

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 27 with name "_SYSSMU27$"
too small
Elapsed: 00:10:08.83

奇怪了。看看今天这个UNTOTBS2 UTILIZATION怎样。

SQL> select snap_time, free_mb from tbs_usage_hist where database='<DB Name>' and tbs='UNDOTBS2' and snap_time>sysdate-1 order by snap_time;
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-15 18:00:00    9172.56
2008-07-15 19:00:00    9172.56
2008-07-15 20:00:00    9156.56
2008-07-15 21:00:00    9188.56
2008-07-15 22:00:00    9204.56
2008-07-15 23:00:00    9212.56
2008-07-16 00:00:00    9228.56
2008-07-16 01:00:00    9228.56
2008-07-16 02:00:00    9236.56
2008-07-16 03:00:00    9228.56
2008-07-16 04:00:00    9252.56
2008-07-16 05:00:00    9252.56
2008-07-16 06:00:00    9252.56
2008-07-16 07:00:00    9260.56
2008-07-16 08:00:00    9244.56
2008-07-16 09:00:00    8486.56
2008-07-16 10:00:00    1683.56
2008-07-16 11:00:00       2.31
2008-07-16 12:00:00       1.94
2008-07-16 13:00:00       2.44
2008-07-16 14:00:00       2.44
2008-07-16 15:00:00       1.25
2008-07-16 16:00:00      17.75

那 问题应当是很明了了,自今天十点多UNDOTBS2一直是HIGHLY UTILIZED。打个电话给APP OWNER,原来他今天早上十点左右做了一个很大的DELETE。即然这个报错的APP只要在二十四小时内能再执行完就可以,而OLTP APP没报错,那就再等等吧。在四点半时,UNDOTBS2就差不多是85% FREE。再试试:
16:37:49 SQL> insert into mytab <the select statement>
182 rows created.
Elapsed: 00:34:47.39
17:12:37 SQL>
现在的UNDOTBS2 UTILIZATION:
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-16 17:00:00    8523.63
问题解决。SNAPSHOT TOO OLD从来就不是一个过时的题目,也没有一个简单的答案。

 

未完待续 (增加数据块头部解释)

转载于:https://www.cnblogs.com/buro79xxd/archive/2010/03/02/1682601.html

<think>我们正在处理关于Oracle数据库中ORA-01555错误的问题。根据提供的引用,我们可以总结出以下关键点: 1. ORA-01555错误是“快照过旧”错误,通常发生在查询需要一致性读时,所需的回滚信息已经被覆盖。 2. 错误原因包括: - 查询执行时间过长 - UNDO表空间过小 - 事务量过大或提交过于频繁 - 使用SQL*Loader直接加载数据时(因为不产生Redo和Undo,使用CACHED COMMIT SCN) 3. 解决方法: - 增加UNDO表空间大小 - 增加`undo_retention`参数(在自动撤销管理模式下) - 优化SQL语句以减少执行时间 - 避免频繁提交事务(合并小事务) - 清理回滚信息(但注意在自动撤销管理下,通常由Oracle管理) - 检查数据库配置(如UNDO表空间大小、回滚存储方式) 此外,引用[5]提到在Sqoop抽取Oracle数据时遇到此错误,也属于上述原因。 现在,我们将按照用户要求,逐步分析和解决ORA-01555错误。 ### 步骤1: 确认错误发生的原因 首先,需要确认错误发生的场景。例如: - 错误是在执行什么操作时发生的?(长时间查询?数据导入?) - 发生的频率如何?(偶尔发生还是经常发生?) ### 步骤2: 检查UNDO表空间配置 在自动撤销管理(AUM)模式下,需要检查: 1. UNDO表空间大小:是否足够? 2. `undo_retention`参数设置:默认900秒(15分钟),如果查询需要更长的读一致性时间,则需要增加此值。 查询当前UNDO表空间使用情况和配置: ```sql -- 查看UNDO表空间大小 SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb FROM dba_data_files WHERE tablespace_name LIKE '%UNDO%'; -- 查看UNDO表空间使用情况 SELECT tablespace_name, status, SUM(bytes)/1024/1024 AS size_mb FROM dba_undo_extents GROUP BY tablespace_name, status; -- 查看undo_retention参数 SHOW PARAMETER undo_retention; ``` ### 步骤3: 优化查询 如果错误由长时间查询引起,考虑: - 优化查询语句(添加索引、重写查询) - 减少查询需要的一致性读时间(例如,在业务低峰期运行) ### 步骤4: 调整事务提交频率 如果因为频繁提交导致UNDO信息被快速覆盖,考虑合并事务(但注意不要使单个事务过大,以免产生其他问题)。 ### 步骤5: 增加UNDO表空间或调整undo_retention 如果UNDO表空间不足,增加其大小: ```sql ALTER DATABASE DATAFILE '/path/to/undo_datafile' RESIZE <new_size>M; ``` 或者添加新的数据文件: ```sql ALTER TABLESPACE undo_tbs ADD DATAFILE '/path/to/new_undo_datafile' SIZE <size>M; ``` 调整`undo_retention`(单位:秒): ```sql ALTER SYSTEM SET undo_retention = <new_value_in_seconds> SCOPE=BOTH; ``` 注意:在Oracle 10g及以上版本中,如果启用了自动扩展,Oracle可能会根据`undo_retention`自动调整UNDO表空间大小。但如果没有自动扩展,则可能需要手动调整。 ### 步骤6: 处理特殊情况 如果错误发生在使用SQL*Loader直接加载数据时,可以考虑: - 避免使用直接加载方式(设置`direct=false`),但这会降低加载速度。 - 或者,在加载后立即执行分析操作,使数据块尽快进入一致状态。 ### 步骤7: 监控和预防 设置监控,定期检查UNDO表空间的使用情况以及长查询情况,预防错误再次发生。 ### 总结 ORA-01555错误的解决需要根据具体原因采取相应措施。通常,调整UNDO表空间大小和`undo_retention`参数是最直接的方法,同时优化相关SQL语句和事务处理逻辑也是关键。 §§相关问题§§ 1. 如何监控Oracle UNDO表空间的使用情况? 2. 在自动撤销管理模式下,如何调整UNDO表空间的大小? 3. 如何优化长时间运行的查询以避免ORA-01555错误? 4. 使用SQL*Loader直接加载数据时,如何避免ORA-01555错误?</think>### ORA-01555错误分析与解决方案 #### **错误本质** ORA-01555Oracle的"快照过旧"错误,发生在查询需要一致性读时,所需的回滚数据已被覆盖[^1][^3]。核心矛盾是:**查询执行时间 > 回滚数据保留时间**。 #### **根本原因分析** 1. **UNDO空间不足** - UNDO表空间过小,无法存储足够的历史数据 - 高并发事务导致UNDO信息快速覆盖 - 检查:`SELECT TABLESPACE_NAME, STATUS, SUM(BYTES)/1024/1024 "SIZE_MB" FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS;` 2. **undo_retention设置不当** - 默认900秒(15分钟),长查询超过此时间会触发错误 - 检查:`SHOW PARAMETER undo_retention` 3. **SQL执行效率低下** - 全表扫描、缺失索引导致查询超时 - 检查:`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'xxx'));` 4. **特殊操作影响** - SQL*Loader直接加载(`direct=true`)不生成Redo/Undo[^2] - 频繁提交事务产生大量瞬时UNDO数据[^4][^5] #### **解决方案** 1. **调整UNDO配置** ```sql -- 增加undo_retention(单位:秒) ALTER SYSTEM SET undo_retention = 1800; -- 扩展UNDO表空间 ALTER DATABASE DATAFILE '/u01/oradata/undotbs01.dbf' RESIZE 10G; ``` 2. **优化SQL语句** - 添加缺失索引:`CREATE INDEX idx_col ON table_name(column);` - 避免全表扫描:使用分区表、物化视图 - 减少结果集:添加`WHERE`条件过滤 3. **事务模式调整** - 合并小事务:减少`COMMIT`频率 - 避免高峰时运行长查询 - SQL*Loader改用常规模式(`direct=false`)[^2] 4. **紧急处理** ```sql -- 强制释放UNDO空间(谨慎使用) ALTER SYSTEM CHECKPOINT; -- 清理过期会话 SELECT SID, SERIAL# FROM V$SESSION WHERE STATUS='INACTIVE'; ALTER SYSTEM KILL SESSION 'sid,serial#'; ``` #### **预防措施** - **监控脚本**: ```sql SELECT TO_CHAR(BEGIN_TIME, 'HH24:MI') TIME, UNDOBLKS, TXNCOUNT, MAXQUERYLEN FROM V$UNDOSTAT WHERE MAXQUERYLEN > 500; -- 查询超过500秒报警 ``` - **参数调整**: - 设置`_undo_autotune=TRUE`(Oracle 10g+自动优化UNDO) - 启用RETENTION GUARANTEE:`ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;` > **关键原则**:该错误本质是资源与需求的矛盾[^4]。优先优化SQL效率,其次调整UNDO配置,最后考虑硬件扩容。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值