长事务(长事务、超过10分钟)
with transaction_details as
( select inst_id
, ses_addr
, sysdate - start_date as diff
from gv$transaction
where start_date <= sysdate - interval '600' second
)
select s.username
, to_char(trunc(t.diff))
|| ' days, '
|| to_char(trunc(mod(t.diff * 24,24)))
|| ' hours, '
|| to_char(trunc(mod(t.diff * 24 * 60,24)))
|| ' minutes, '
|| to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
|| ' seconds' as transaction_duration
, s.program
, s.terminal
, s.event
, s.sql_id
, s.sid
, s.serial#
, s.INST_ID
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc ;