查询长事务

模拟长事务

create tablespace test datafile size 30g autoextend off;
create user test identified by test default tablespace test;
grant dba to test;

create table test as select * from dba_objects;
insert into test select * from test;
/
/
/

查看长事务

-- 查询长事务
set linesize 230
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
/

非CDB

SELECT  trans_cnt,
    round(max_blocks * 1000, 2) as max_blocks,
    round(max_duaration, 0) as max_duaration
    FROM (select   count(*) trans_cnt,
                            nvl(max(used_ublk), 0) / 1000 max_blocks,
                            nvl((sysdate - min(to_date(start_time, 'mm/dd/yy hh24:mi:ss'))),
                                    0) * 1440 * 60 max_duaration
    FROM v$transaction);

CDB

SELECT    con_id,
    trans_cnt,
    round(max_blocks * 1000, 2) as max_blocks,
    round(max_duaration, 0) as max_duaration
    FROM (select con_id,
                            count(*) trans_cnt,
                            nvl(max(used_ublk), 0) / 1000 max_blocks,
                            nvl((sysdate - min(to_date(start_time, 'mm/dd/yy hh24:mi:ss'))),
                                    0) * 1440 * 60 max_duaration
    FROM v$transaction
    group by con_id);