Identify active transactions in undo and rollback segments

最新推荐文章于 2023-05-04 09:36:45 发布

原创最新推荐文章于 2023-05-04 09:36:45 发布 · 316 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#transactions #session

Oracle Scripts 专栏收录该内容

45 篇文章

订阅专栏

本文提供了一个SQL查询示例，用于从Oracle数据库中检索当前活跃的事务信息，包括用户、会话ID、事务开始时间等关键数据。

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

Identify active transactions in undo and rollback segments

col o format a10
col u format a10
select osuser o, username u, sid,
segment_name s, substr(sa.sql_text,1,200) txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
And substr(sa.sql_text,1,200) is not null
order by 3;

col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select username, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/