--  activetrans.sql -------------------------------------------------------------------
--
--  Description: Transactions - Show active
--
--  Library: tuning
--
--  Author: 
--
--  Usage: @oracle/tuning/activetrans.sql
--
--  Revision History:
--
--  DATE       WHO		   WHAT
--  ---------  ----------  ------------------
--
--
----------------------------------------------------------------------------------------
column username format a15 word_wrapped
column osuser format a15 word_wrapped
column program format a15 word_wrapped
select t.log_io, t.phy_io, t.used_urec, t.cr_get, t.cr_change, 
s.lockwait, t.start_time, s.username, s.osuser, s.program, 
l.ctime lockctime, l.type locktype, r.name rollbackseg,
decode(t.space, 'YES', 'SPACE TX',
          decode(t.recursive, 'YES', 'RECURSIVE TX',
             decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status
from v$transaction t, v$session s, v$lock l, v$rollname r
where t.ses_addr = s.saddr
and s.sid = l.sid(+)
and (l.type is null or l.type = 'TX')
and t.xidusn = r.usn
;