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

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License