查看Oracle 数据库中的长事务

msdnchina

于 2016-02-10 20:49:41 发布

阅读量1w

点赞数

文章标签： detect long transaction

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文深入探讨了如何通过SQL查询优化技术来解决长运行事务问题，提供了具体示例和执行效果分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文是转载，原文链接：

http://www.hvr-software.com.cn/about/blog/5-long-running-transactions

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
/

如上语句执行之后的效果如下：