--
--To check current all undo segments
--
select s.usn,
n.name,
extents,
Round(rssize/(1024*1024)) rssize_MB,
Round(hwmsize/(1024*1024)) hwmsize_MB,
s.OPTSIZE,
s.SHRINKS,
s.WRAPS,
s.EXTENDS
from v$rollstat s,v$rollname n
where s.usn = n.usn;
--
--to check all historical undo activities
--
set linesize 160
set pagesize 60
alter session set nls_date_format = "yy-mm-dd hh24:mi:ss";
col begin_time head 'begin|time'
col txncount for 99,999 head 'txn|cnt'
col maxquerylen for 99,999 head 'max|query'
col maxconcurrency for 9,999 head 'max|concr|txn'
col bks_per_sec for 99,999 head 'blks/sec'
col kb_per_second for 99,999 head 'kb/|sec'
col undo_mb_required for 9,999,999 head 'mb|undo|needed'
col ssolderrcnt for 9,999 head 'ora-1555|cnt'
col nospaceerrcnt for 9,999 head 'no|space|cnt'
col unxpstealcnt for 99,999 head 'unxp|steal|cnt'
col unxpblkrelcnt for 99,999 head 'unxp|blkrel|cnt'
col unxpblkreucnt for 99,999 head 'unxp|blkreu|cnt'
col expstealcnt for 99,999 head 'exp|steal|cnt'
col expblkrelcnt for 99,999 head 'exp|blkrel|cnt'
col expblkreucnt for 99,999 head 'exp|blkreu|cnt'
break on report
compute max of txncount -
maxquerylen -
maxconcurrency -
bks_per_sec -
kb_per_second -
undo_mb_required -
ssolderrcnt -
nospaceerrcnt -
unxpstealcnt -
unxpblkrelcnt -
unxpblkreucnt -
expstealcnt -
expblkrelcnt -
expblkreucnt on report
select begin_time,
txncount-lag(txncount) over (order by end_time) as txncount,
maxquerylen,
maxconcurrency,
undoblks/((end_time - begin_time)*86400) as bks_per_sec,
(undoblks/((end_time - begin_time)*86400)) * t.block_size/1024 as kb_per_second,
round((undoblks/((end_time - begin_time)*86400)
* t.block_size
* to_number(p2.value)
+ to_number(p2.value)*24
)/1024/1024
) as undo_mb_required,
ssolderrcnt,
nospaceerrcnt,
unxpstealcnt,
unxpblkrelcnt,
unxpblkreucnt,
expstealcnt,
expblkrelcnt,
expblkreucnt
from v$undostat s,
dba_tablespaces t,
v$parameter p,
v$parameter p2
where t.tablespace_name = upper(p.value)
and p.name = 'undo_tablespace'
and p2.name = 'undo_retention'
order by begin_time;
show parameter undo
clear computes
