标题: Oracle查询哪些用户引起的锁，以及锁的对象 [打印本页] 作者: mahan 时间: 2024-12-1 17:22 标题: Oracle查询哪些用户引起的锁，以及锁的对象 1. 查询哪些用户引起的锁，以及锁的对象
select t2.owner,t2.object_name,t2.object_type,t3.machine,t3.sid,t3.serial# from gv$locked_object t1,dba_objects t2,gv$session t3
where t1.object_id = t2.object_id and t1.session_id=t3.sid;
alter system kill session '24,111'; 杀死一个进程,其中24,111分别是上面查询出的sid,serial#
alter system kill session '35,19' immediate;
2.查询哪个用户引起的锁表，以及锁表的SQL语句
select l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,s.user#,l.os_user_name,s.machine,s.terminal,a.sql_text,a.action
from gv$sqlarea a, gv$session s, gv$locked_object l
where l.session_id = s.sid and s.prev_sql_addr = a.address
order by sid, s.serial#;
3. 查看锁表sid和serial，引起锁表的事件
SELECT T2.SID,
T2.SERIAL#,
T3.OWNER,
T3.OBJECT_NAME,
t2.event,blocking_session as b_sid,final_blocking_session as f_b_sid,
t2.p1,t2.p2,t2.p3,round(t2.wait_time_micro/1E6,4) waittime,
T2.LOGON_TIME,t1.LOCKED_MODE
FROM GV$LOCKED_OBJECT T1, GV$SESSION T2, DBA_OBJECTS T3
WHERE T1.SESSION_ID = T2.SID and t1.INST_ID=t2.INST_ID
AND T1.OBJECT_ID = T3.OBJECT_ID
ORDER BY T2.LOGON_TIME;
注：如果一直没有锁表的语句出现，那可能是对应的表在做DDL操作，如添加索引，也可能引发锁表。
4. 查看锁表的用户和会话，登录的事件
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM gv$locked_object l, all_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;