rman备份undotbs1非常慢排查

长事务对rman备份undo表空间的影响:长事务会导致undo段无法释放，undo表空间使用量增加

SELECT s.sid, s.serial#, s.username, s.status, 
       t.start_time, t.used_ublk, t.used_urec,
       (SYSDATE - t.start_date) * 24 * 60 AS duration_minutes
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE (SYSDATE - t.start_date) * 24 * 60 > 60
复制

3.确定事务的会话id和sql

SELECT 
    t.addr AS trans_addr,
    t.start_time,
    t.status,
    t.used_ublk AS undo_blocks,
    t.used_urec AS undo_records,
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.sql_id,
    sq.sql_text
FROM 
    v$transaction t
JOIN 
    v$session s ON t.ses_addr = s.saddr
LEFT JOIN 
    v$sql sq ON s.sql_id = sq.sql_id
WHERE 
     to_date(t.start_time,'yyyy-mm-dd hh24:mi:ss') < SYSDATE - INTERVAL '1' HOUR;  
复制

4.sql对应的表查询已经不存在了

SELECT tablespace_name, status, SUM(bytes)/1024/1024 AS used_mb
FROM dba_undo_extents
WHERE tablespace_name IN ('UNDOTBS1', 'UNDOTBS2')
GROUP BY tablespace_name, status;
复制

6.查看事务关联的undo段

SELECT 
    s.username,
    t.xidusn AS undo_segment_number,
    t.status,
    t.start_time
FROM 
    v$transaction t
JOIN 
    v$session s ON t.ses_addr = s.saddr;
复制

7.终止事务

ALTER SYSTEM KILL SESSION '4126,63655' immediate;

active状态的被事务占用的已经消失。