Hi

Oracle recomends:

OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE * UNDO_BLOCK_PERSECOND)

you can get "ACTUAL UNDO SIZE" 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#

and UNDO BLOCKS PER SECOND from:

SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400)
FROM v$undostat

But its always very high........set according to your resources and requirement.

Regards

Probably not,

Because in DW environment you don´t have usually DML operations, so you will not have to deal with consistent view.

Regards
Helio Dias
Http://heliodias.com

I have one merge query runs for 5 hours on DW env. Should I have set the same hours to undo retention ?