UNDO Usage
select s.snap_id,s.start_time,s.end_time,
x.tablespace_name,
(x.tablespace_size*x.block_size/1024/1024)*.8,
x.tablespace_usedsize*x.block_size/1024/1024
from
(select
dhtsu.snap_id,dbat.tablespace_name,dhtsu.tablespace_size,dhtsu.tablespace_maxsize,
dhtsu.tablespace_usedsize ,dbat.block_size
from DBA_HIST_TBSPC_SPACE_USAGE dhtsu,v$tablespace vt,dba_tablespaces dbat
where
dhtsu.tablespace_id = vt.ts# and
vt.name in
(
select value from v$parameter where upper(name)= 'UNDO_TABLESPACE'
) and
vt.name = dbat.TABLESPACE_NAME
)x,
(
select snap_id,
max(to_char((trunc(begin_interval_time,'mi')),'DD/MON/YYYY HH24:MI:SS')) as start_time,
max(to_char((trunc(end_interval_time, 'mi')),'DD/MON/YYYY HH24:MI:SS')) as end_time
from dba_hist_snapshot
where begin_interval_time >= sysdate - 60
group by snap_id
) s
where s.snap_id = x.snap_id
order by x.tablespace_name,snap_id
Download Speedway to graphically plot the above script results.