ORA-01555: snapshot too old error in oracle

Leave a reply

ORA-01555: snapshot too old during EXPDP datapump 

Undo Retention is the minimum time (in seconds) that oracle system retains undo for at least the time specified in this parameter.

The amount of time for which undo is retained for the Oracle Database for the current undo table space can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

UNDO_RETENTION parameter dependent upon the undo tablespace has enough space for keeping the undo data.If undo data goes beyond the undo tablespace limit then cause “snapshot too old” message.

Error

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

If you get the ORA-1555: snapshot too old then you need to set the parameter undo)retention to avoid the error.

Use the Undo Advisory forget the optimal value of retention and size as below:

Get the optimal value of parameter undo_retention(in secs) 

Get optimal undo retention in sec
==============================================

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'
/

Needed UNDO Size for Database Activity


You may need to increase the size of undo tablespace, you can use following query to get optimal size of undo tablespace needed.


UNDO Size for given Database Activity

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  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'
/

Increase the undo retention time(in secs) with following command:

ALTER SYSTEM SET UNDO_RETENTION = 2400; 

ALTER SYSTEM SET UNDO_RETENTION = 2400 scope=both;

Increase UNDO Tablespace Size

-- increase the size of undo datafile
ALTER DATABASE DATAFILE '/path/undo01.dbf' RESIZE 20G;

-- add more files to undo tablespace
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/path/undo02.dbf' SIZE 20G;

Leave a Reply