Oracle Database Discussions

dear gurus

want to understand the concept of undo_retention as geeting a bit confused

OS is rhel 4.5
oracle 10.2.0.4

there is a job scheduled in my database that regularly throws error of snapshot too old error.so i am planning to increase undo_retention but i am confused that what value should i give.as i runned one query from google as

SQL> show parameter db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
4 5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
11 12 13 AND a.ts# = b.ts#
) d,
14 15 v$parameter e,
16 v$parameter f,
17 (
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
18 19 undo_block_per_sec
FROM v$undostat
) g
20 21 22 WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size' 23 ;

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
1580 900 49772


so should i set it to 49772.

my flashback setting is as

SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

is undo_retention also related with flashback setting .also confirm undo_retention is dynamic parameter i could change it at anytime without affecting anything.

Rgds