常用sql语句-查看长事务session

长事务(长事务、超过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  ;

Related Posts