解决oracle修改未提交导致锁表问题

-- 查找阻塞会话与被阻塞会话

SELECT s1.sid AS blocker_sid, s2.sid AS blocked_sid, 
       l1.type AS lock_type, o.object_name
FROM v$lock l1
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s2 ON l2.sid = s2.sid
JOIN dba_objects o ON l1.id1 = o.object_id
WHERE l1.block > 0 AND l2.request > 0;

-- 通过事务视图定位长时间未提交的事务

SELECT s.sid, s.serial#, s.username, t.start_time, 
       t.status, t.used_ublk
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr
WHERE t.status = 'ACTIVE';

-- 杀掉对应会话

alter system kill session '1025,41';  -- sid和serial# 共同确定一条数据