oracle-01555错误

最新推荐文章于 2025-06-26 21:29:46 发布

原创最新推荐文章于 2025-06-26 21:29:46 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#integer #string #system #database #sql #action

本文介绍了解决Oracle数据库中ORA-01555错误的方法，该错误通常由于快照过旧导致。文章提供了调整undo_retention参数的具体步骤，并给出了计算最佳undo保留时间的SQL查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天再跑batch的时候报错如下：

ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small

$ oerr ora 1555

01555, 00000, "snapshot too old: rollback segment number %s with name /"%s/" too small"

// *Cause: rollback records needed by a reader for consistent read are

// overwritten by other writers

// *Action: If in Automatic Undo Management mode, increase undo_retention

// setting. Otherwise, use larger rollback segments

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 4 16:01:08 2009

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

运行如下的sql可以获取“Optimal Undo Retention”

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       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 (
       SELECT SUM(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,
       (
       SELECT MAX(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'
/

解决方法：

SQL> alter system set undo_retention=1800;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     1800

undo_tablespace                      string      UNDOTBS1

扩大endo_retention

(时间问题，只是为了记录一下，所以写的乱！)