oracle-01555错误

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

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

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

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