Skip to Main Content
  • Questions
  • historic undo usage by day( last 90 days)


Question and Answer

Chris Saxon

Thanks for the question, Bakhsish.

Asked: November 10, 2016 - 7:47 pm UTC

Last updated: November 22, 2023 - 1:58 pm UTC


Viewed 10K+ times! This question is

You Asked

I am trying to size my undo tablespace properly( requirement is 24 hours retention for flashback query), So I need historic undo usage per day for last 90 days. and I am using the query below but I suspect that it is giving me wrong answer. Autoextend is off for undo files.

set pages 200
select substr( time,1,8) ,sum(used_GB) , sum(TUNED_UNDORETENTION ) from (
select to_char(begin_time,'yyyymmdd hh24') time ,
from dba_hist_undostat
where begin_time > sysdate - 90
group by to_char(begin_time,'yyyymmdd hh24'), TUNED_UNDORETENTION
order by to_char(begin_time,'yyyymmdd hh24')) a
group by substr( time,1,8) order by 1 ;


---------------- ------------ ------------------------
20160901 2428.11 287718

Please help me determine how much( MAX ) historically undo has been used over 24 hours,

Thank you

Also this is my current undo size ,undo_retention.

---------- ------------------------- ----------------------
354818 300 12292
1 row selected.
20161101 1779.3 226369
20161001 1747.59 277725
20161105 1728.3 265862

and Chris said...

Yeah, you can't just sum up the tuned_undoretention values!

To find the optimal size for your undo tablespace, use the Undo Advisor. The recommended way is via EM. This allows you to pass in your Flashback Query duration.

You can find instructions on how to do this in EM at:


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


Tanmoy Dey, November 22, 2023 - 12:15 pm UTC

Please use this query to check the undo usage history

with t as (
select ss.run_time,,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and su.tablespace_id = ts.ts#
and in ('UNDOTBS1')
and = dt.tablespace_name )
select e.run_time,,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb, (e.used_size_gb - b.used_size_gb) as variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;
Chris Saxon
November 22, 2023 - 1:58 pm UTC

Thanks for sharing. Worth noting that you need to have AWR enabled and licensed to use it to run this query.