Oracle 查询数据库长事物相关信息

查询长事物下的时间、以及占用Undo，根据时间判断当前的SID与serial# 

set linesize 200
set pagesize 5000
col transaction_duration format a40
col program format a15
col username format a15


with transaction_details as
( select inst_id
,start_date
  , ses_addr
  , sysdate - start_date as diff
  ,xidusn
from gv$transaction
 order by start_date)
select
t.inst_id
,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#
,t.start_date
,  r.rssize/1024/1024
from gv$session s
, transaction_details t
,gv$rollstat r
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
and t.xidusn = r.usn
order by t.diff desc
/

在数据库中查询长事物下的时间、以及占用Undo、以及当前的SQL,根据SID与serial#,判断当前没有SQL，同时对业务的影响做出判断。 

set linesize 200
set pagesize 5000
col transaction_duration format a40
col program format a15
col username format a15

with transaction_details as
( select inst_id
,start_date
  , ses_addr
  , sysdate - start_date as diff
  ,xidusn
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#
,t.start_date
,  r.rssize/1024/1024
,sq.sql_text
from gv$session s
, transaction_details t
,gv$rollstat r
,gv$sqltext  sq
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
and t.xidusn = r.usn
and s.sql_address=sq.address
order by t.diff desc
/

 

如果没有，即对会话进行Kill 

alter system kill session '999,40840' immediate;