07-ORA-01555快照过旧

ORA-01555快照过旧——undo太小还是查询太久社保系统的月底对账查询跑了20分钟跑到第15分钟时报ORA-01555。不是因为没设undo是undo被覆盖了。这篇文章拆开ORA-01555的三种原因和对应修法。文章目录ORA-01555快照过旧——undo太小还是查询太久一、ORA-01555 是什么意思二、三种原因三、诊断四、三种修法五、避免 ORA-01555 的底线配置一、ORA-01555 是什么意思ORA-01555: snapshot too old: rollback segment number 8 with name _SYSSMU8_1234567890$ too smallOracle 执行一条长查询时需要保证查询过程中看到的所有数据都是查询开始时刻的快照。如果某行在查询开始后被修改了Oracle 用 undo 段来还原它。如果 undo 被覆盖了——就报 ORA-01555。一句代码都没写错就是 undo 不够大或者查询太慢。二、三种原因原因场景症状undo表空间太小批量更新同时跑长查询偶发undo高峰期出问题undo_retention太短事务提交后undo很快被覆盖查询10分钟常出查询本身太慢全表扫描398MB的表查询本身需要优化社保系统最常见的是第三种——对账查询扫描PAYMENT_HISTORY398MB跑了20分钟期间有单位缴费录入在更新同表数据undo被覆盖。三、诊断-- 查看undo表空间使用情况SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024,2)ASMB,ROUND(SUM(MAXBYTES)/1024/1024,2)ASMAX_MBFROMDBA_DATA_FILESWHERETABLESPACE_NAMELIKE%UNDO%GROUPBYTABLESPACE_NAME;-- 查看undo retention秒SHOWPARAMETER undo_retention;-- 查看当前undo实际使用SELECTSTATUS,COUNT(*),ROUND(SUM(BYTES)/1024/1024,2)ASMBFROMDBA_UNDO_EXTENTSGROUPBYSTATUS;-- 最近7天的undo量SELECTTO_CHAR(BEGIN_TIME,YYYY-MM-DD HH24)ASHOUR,ROUND(SUM(UNDOBLKS*8)/1024,2)ASUNDO_MBFROMV$UNDOSTATWHEREBEGIN_TIMESYSDATE-7GROUPBYTO_CHAR(BEGIN_TIME,YYYY-MM-DD HH24)ORDERBYHOUR;四、三种修法修法一加大 undo 表空间-- 看当前大小SELECTFILE_NAME,BYTES/1024/1024ASMBFROMDBA_DATA_FILESWHERETABLESPACE_NAMEUNDOTBS1;-- 加2GBALTERTABLESPACEUNDOTBS1ADDDATAFILE/u01/app/oracle/oradata/orcl/undo02.dbfSIZE2G;-- 或让现有的自动扩展ALTERDATABASEDATAFILE/u01/app/oracle/oradata/orcl/undo01.dbfAUTOEXTENDONNEXT512M MAXSIZE16G;注意加大 undo 不是无脑的。undo 数据文件有预分配特性——Oracle 可能先占满空间再回收。社保系统 4GB 的 undo 够用了。修法二延长 undo_retention 开启担保ALTERSYSTEMSETundo_retention3600;-- 1小时默认900秒15分钟-- 开启retention担保——Oracler 10g需要undo表空间自动扩展ALTERTABLESPACEUNDOTBS1 RETENTION GUARANTEE;RETENTION GUARANTEE打开后Oracle 宁可让事务报错undo不足也不会让快照失效。前提是 undo 表空间足够大。修法三优化长查询修法一和二是给查询多争取时间。但真正该做的是让查询跑快一点。跑5分钟的表查询不需要 undo retention 设1小时。-- 给查询加索引、改写SQL、加并行度SELECT/* PARALLEL(4) */...五、避免 ORA-01555 的底线配置-- undo表空间至少8GBALTERSYSTEMSETundo_tablespaceUNDOTBS1SCOPESPFILE;-- retention至少30分钟1800秒ALTERSYSTEMSETundo_retention1800;-- 开启担保ALTERTABLESPACEUNDOTBS1 RETENTION GUARANTEE;再加一条所有的月底对账查询放在凌晨跑——没人在这个时段做批量UPDATEundo不会被覆盖。✅ 亮点从ORA-01555的三种原因出发分别给诊断SQL和修法SQL。扩展方向Flashback QueryAS OF TIMESTAMP、TEMP表空间临时段ORA-01652ORA-01555与ORA-01683的关联。