UNDO TABLESPACE QUERIES

CALCULATE UNDO_RETENTION 

----Total Size of undo tablespace 
select trim(sum(d.bytes)) "undo"
from v$datafile d,
v$tablespace t,
dba_tablespaces s
where s.contents='UNDO'
and s.status='ONLINE'
and t.name=s.tablespace_name
and d.ts#=t.ts#;



---Generate Undo_per_sec
select max(undoblks/((end_time-begin_time)*3600*24)) "undo_block_per_sec" from v$undostat;




---Calculate formula Undo_retention time in sec.
Undo_retention = undo/(undo_bloc_per_sec*db_block_size)

so undo_retention set round of figure.




------Oracle Automatically tunes Undo Retention in the current instance workload
select to_char(begin_time,'hh24:mi:ss') BEGIN_TIME,
to_char(end_time,'hh24:mi:ss') END_TIME,
maxquerylen,nospaceerrcnt,tuned_undoretention from v$undostat;


select to_char(begin_time,'hh24:mi:ss') BEGIN_TIME,
to_char(end_time,'hh24:mi:ss') END_TIME,
maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount  from v$undostat order by undoblks;




----Undo Usage
select ( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%') allocated,(select sum(bytes)/1024/1024
from dba_free_space where tablespace_name like 'UND%') free,(select sum(bytes)/1024/1024
from dba_undo_extents where tablespace_name like 'UND%') USed from dual
/



----Undo Used by session
select s.sid,s.username,t.used_urec,t.used_ublk
from v$session s,v$transaction t
where s.saddr=t.ses_addr
and s.sid=107
order by t.used_ublk desc;



select s.sid,t.statistic#,s.value
from v$sesstat s,v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'
order by s.value desc;



---most undo behalf of username and sid
select sql.sql_text sql_text,t.used_urec records,t.used_ublk blocks,(t.used_ublk*8192/1024) kbytes from v$transaction t,
v$session s,
v$sql sql
where t.addr=s.taddr
and s.sql_id=sql.sql_id
and s.username='&USERNAME';