Undo sizing

SELECT
  inst_id,
  MAX(required_undo_mb)
FROM
(
  SELECT
    inst_id,
    SUM(undo_size) OVER (partition BY inst_id ORDER BY begin_interval_time ROWS 72 preceding) required_undo_mb
  FROM
  (
    SELECT
      s.instance_number inst_id,
      s.begin_interval_time,
      round((ss.VALUE - lag(ss.VALUE) OVER (partition BY s.instance_number ORDER BY s.begin_interval_time))/1024/1024) undo_size
    FROM
      dba_hist_snapshot s,
      v$database d,
      dba_hist_sysstat ss,
      v$statname n
    WHERE
      s.dbid = d.dbid AND
      s.dbid = ss.dbid AND
      s.instance_number = ss.instance_number AND
      s.snap_id = ss.snap_id AND
      ss.stat_id = n.stat_id AND
      n.name = 'undo change vector size'
  )
)
GROUP BY inst_id
;