正德厚生,臻于至善

查找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)

select XIDUSN, XIDSLT, XIDSQN , status , event,event_time from dba_logstdby_events order by event_time;

参考文章
https://www.cnblogs.com/kerrycode/p/10930004.html
https://blog.csdn.net/msdnchina/article/details/50650227

赞(0) 打赏
未经允许不得转载:徐万新之路 » 查找oracle数据库中的长事务
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏