Find long running transaction that are running for more than 5 minutes with transaction id.
col machine format a30
col sid format 99999
col Transaction_id format a20
col username format a10
col days format 99.99
col minutes format 999999
set lines 120 pages 200
select a.sid, a.username, a.machine,
round((sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 1440) minutes,
round((sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)), 2) days,
a.status, b.status, b.xidusn||’.’||b.xidslot||’.’||b.xidsqn Transaction_id
from v$session a , v$transaction b
where a.SADDR = b.SES_ADDR
and (sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 1440 > 5
order by (sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 1440
/