col machine format a30
col sid format 99999
col serial# format 99999
col sid_info format a14
col Transaction_id format a20
col username format a8
col days format 99.99
col hours1 format 999999
col TX_HRS format 999
col USER_HRS format 999
col LOCK format a2
set lines 120 pages 200
select a.sid || ‘,’ || a.serial# sid_info, a.username, a.machine,
round((sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 24) “TX_HRS”,
round((sysdate – a.logon_time) * 24) “USER_HRS”,
a.status, b.status, b.xidusn||’.’||b.xidslot||’.’||b.xidsqn Transaction_id,
decode(UBAFIL,0, decode(UBABLK, 0, decode(UBASQN, 0, ‘N’, ‘Y’), ‘Y’), ‘Y’) “LOCK”
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
— and (UBAFIL != 0 and UBABLK != 0 and UBASQN != 0)
order by (sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 1440
/
