7 Revisions

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

EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
EXP-00000: Export terminated unsuccessfully


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

Get the optimal value of parameter undo_retention(in secs)

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.

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:

show parameter undo_rentention
ALTER SYSTEM SET UNDO_RETENTION = 2400 scope=both;