oracle automatic undo retention - ghdrako/doc_snipets GitHub Wiki

automatic undo retention

$cat undo_session_usage.sql
-- Check the undo usage by session
--set linesize 180 trimout on trimspool on
col sid     format 9999999999
col serial# format 999999999
col orauser format a30
col program format a30
col undoseg format a30
col "Undo"  format a20

select
s.sid,s.serial#,
NVL(s.username, 'NA') orauser,
s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
from
sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
where s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';