Check UNDO is Configured Correctly
-- -- Check UNDO is Configured Correctly -- SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 SET VERIFY OFF SELECT d.undo_size/(1024*1024) as UNDO_SIZE, SUBSTR(e.value,1,25) as UNDO_RETENTION, ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) as OPTIMUM_UNDO_RETENTION 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' /
Published 24th September 2023