模拟长事务
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);