oracle-01555错误

最新推荐文章于 2022-01-05 14:15:54 发布

阿杜S考特

最新推荐文章于 2022-01-05 14:15:54 发布

阅读量1.4k

点赞数

文章标签： integer string system database sql action

本文链接：https://blog.csdn.net/zxdu721/article/details/4517162

版权

今天再跑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

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