Oracle查询哪些用户引起的锁，以及锁的对象

mahan

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#;

锁的模式
v$locked_object中的LOCKED_MODE字段表示锁的模式，oracle中锁的模式有如下几种:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive

数字越大锁级别越高, 影响的操作越多。

1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, “resource busy and acquire with NOWAIT specified”
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive