查找oracle数据库中的长事务

set linesize 200
set pagesize 5000
col transaction_duration format a45
 
with transaction_details as
( select inst_id
  , ses_addr
  , sysdate - start_date as diff
  from gv$transaction
)
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.status
, s.sid
, s.serial#
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
/

 

运行上面的sql，你可以找到还未提交的长事务，但是你不一定能找到该事务正在运行的sql。原因请参（https://www.cnblogs.com/kerrycode/p/10930004.html）

 

 

参考文章

https://www.cnblogs.com/kerrycode/p/10930004.html

https://blog.csdn.net/msdnchina/article/details/50650227