Oracle 查看 对象 持有 锁 的情况

 

有关Oracle 锁的说明，参考：

ORACLE锁机制

http://blog.csdn.net/tianlesoftware/article/details/4696896

用如下SQL 查看一下系统中相关对象上锁的情况：

[sql] view plain copy print ?

1. <pre name="code" class="sql">/* Formattedon 2012/2/13 14:24:32 (QP5 v5.185.11230.41888) */ 
2. SELECT S.SID SESSION_ID, 
3.        S.USERNAME, 
4.        DECODE (LMODE, 
5.                0, ' None ', 
6.                1, ' Null ', 
7.                2, ' Row-S(SS) ', 
8.                3, ' Row-X(SX) ', 
9.                4, ' Share', 
10.                5, 'S/Row-X (SSX) ', 
11.                6, 'Exclusive ', 
12.                TO_CHAR (LMODE)) 
13.           MODE_HELD, 
14.        DECODE (REQUEST, 
15.                0, ' None ', 
16.                1, ' Null ', 
17.                2, ' Row-S(SS) ', 
18.                3, ' Row-X(SX) ', 
19.                4, ' Share', 
20.                5, 'S/Row-X (SSX) ', 
21.                6, 'Exclusive ', 
22.                TO_CHAR (REQUEST)) 
23.           MODE_REQUESTED, 
24.        O.OWNER || ' . ' || O.OBJECT_NAME ||' ( ' || O.OBJECT_TYPE || ' ) ' 
25.           AS OBJECT_NAME, 
26.        S.TYPE LOCK_TYPE, 
27.        L.ID1 LOCK_ID1, 
28.        L.ID2 LOCK_ID2 
29.   FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S 
30. WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_IDAND object_name = 'xxxx'; 

<pre name="code" class="sql">/* Formatted on 2012/2/13 14:24:32 (QP5 v5.185.11230.41888) */ SELECT S.SID SESSION_ID, S.USERNAME, DECODE (LMODE, 0, ' None ', 1, ' Null ', 2, ' Row-S(SS) ', 3, ' Row-X(SX) ', 4, ' Share', 5, 'S/Row-X (SSX) ', 6, 'Exclusive ', TO_CHAR (LMODE)) MODE_HELD, DECODE (REQUEST, 0, ' None ', 1, ' Null ', 2, ' Row-S(SS) ', 3, ' Row-X(SX) ', 4, ' Share', 5, 'S/Row-X (SSX) ', 6, 'Exclusive ', TO_CHAR (REQUEST)) MODE_REQUESTED, O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) ' AS OBJECT_NAME, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND object_name = 'xxxx';

该SQL 显示所有对象上的锁，如果要查某个具体的对象，可以根据OBJECT_NAME 字段进行一下过滤，找到对应的SID 之后去查V$SESSION 视图。

该视图会显示session 对应的信息，包括终端的信息，如果找到了终端，可以让它提交或者回滚一下就OK了。 我这里是测试环境，直接把session kill 掉了。然后修改表就ok了。

[sql] view plain copy print ?

1. SQL>alter system kill session'SID,SERIAL#' 

SQL>alter system kill session 'SID,SERIAL#'

后来测试了发现，上面的脚本有时候会查不到对象的的相关记录，故增加了V$ACCESS视图，通过对这个视图进行对象的判断，修改之后的脚本如下：

[sql] view plain copy print ?

1. SELECT S.SID SESSION_ID, 
2.        S.USERNAME, 
3.        DECODE (LMODE, 
4.                0, ' None ', 
5.                1, ' Null ', 
6.                2, ' Row-S(SS) ', 
7.                3, ' Row-X(SX) ', 
8.                4, ' Share', 
9.                5, 'S/Row-X (SSX) ', 
10.                6, 'Exclusive ', 
11.                TO_CHAR (LMODE)) 
12.           MODE_HELD, 
13.        DECODE (REQUEST, 
14.                0, ' None ', 
15.                1, ' Null ', 
16.                2, ' Row-S(SS) ', 
17.                3, ' Row-X(SX) ', 
18.                4, ' Share', 
19.                5, 'S/Row-X (SSX) ', 
20.                6, 'Exclusive ', 
21.                TO_CHAR (REQUEST)) 
22.           MODE_REQUESTED, 
23.        O.OWNER || ' . ' || O.OBJECT_NAME ||' ( ' || O.OBJECT_TYPE || ' ) ' 
24.           AS OBJECT_NAME, 
25.        S.TYPE LOCK_TYPE, 
26.        L.ID1 LOCK_ID1, 
27.        L.ID2 LOCK_ID2 
28.   FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S,V$ACCESS A 
29. WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND S.SID=A.SIDAND A.OBJECT= 'PROC_VALIDATE_RULE_V3'; 

SELECT S.SID SESSION_ID, S.USERNAME, DECODE (LMODE, 0, ' None ', 1, ' Null ', 2, ' Row-S(SS) ', 3, ' Row-X(SX) ', 4, ' Share', 5, 'S/Row-X (SSX) ', 6, 'Exclusive ', TO_CHAR (LMODE)) MODE_HELD, DECODE (REQUEST, 0, ' None ', 1, ' Null ', 2, ' Row-S(SS) ', 3, ' Row-X(SX) ', 4, ' Share', 5, 'S/Row-X (SSX) ', 6, 'Exclusive ', TO_CHAR (REQUEST)) MODE_REQUESTED, O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) ' AS OBJECT_NAME, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S,V$ACCESS A WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND S.SID=A.SID AND A.OBJECT= 'PROC_VALIDATE_RULE_V3';

再次修改了一下，添加了V$SQL视图，这样可以一起查出具体导致这种锁的SQL语句，一次性就搞定了，SQL如下：

[sql] view plain copy print ?

1. /* Formatted on 2012/6/6 10:59:49 (QP5 v5.185.11230.41888) */ 
2. SELECT distinct S.SID SESSION_ID, 
3.        S.STATUS, 
4.        S.USERNAME, 
5.        DECODE (LMODE, 
6.                0, ' None ', 
7.                1, ' Null ', 
8.                2, ' Row-S(SS) ', 
9.                3, ' Row-X(SX) ', 
10.                4, ' Share', 
11.                5, 'S/Row-X (SSX) ', 
12.                6, 'Exclusive ', 
13.                TO_CHAR (LMODE)) 
14.           MODE_HELD, 
15.        DECODE (REQUEST, 
16.                0, ' None ', 
17.                1, ' Null ', 
18.                2, ' Row-S(SS) ', 
19.                3, ' Row-X(SX) ', 
20.                4, ' Share', 
21.                5, 'S/Row-X (SSX) ', 
22.                6, 'Exclusive ', 
23.                TO_CHAR (REQUEST)) 
24.           MODE_REQUESTED, 
25.        O.OWNER || ' . ' || O.OBJECT_NAME ||' ( ' || O.OBJECT_TYPE || ' ) ' 
26.           AS OBJECT_NAME, 
27.        S.TYPE LOCK_TYPE, 
28.        L.ID1 LOCK_ID1, 
29.        L.ID2 LOCK_ID2, 
30.        S2.SQL_TEXT 
31.   FROM V$LOCK L, 
32.        SYS.DBA_OBJECTS O, 
33.        V$SESSION S, 
34.        V$ACCESS A, 
35.        V$SQL S2 
36. WHERE     L.SID = S.SID 
37.        AND L.ID1 = O.OBJECT_ID 
38.        AND S.SID = A.SID 
39.        AND S2.HASH_VALUE = S.SQL_HASH_VALUE 
40.        AND A.OBJECT = 'PROC_VALIDATE_RULE_V3'; 

/* Formatted on 2012/6/6 10:59:49 (QP5 v5.185.11230.41888) */ SELECT distinct S.SID SESSION_ID, S.STATUS, S.USERNAME, DECODE (LMODE, 0, ' None ', 1, ' Null ', 2, ' Row-S(SS) ', 3, ' Row-X(SX) ', 4, ' Share', 5, 'S/Row-X (SSX) ', 6, 'Exclusive ', TO_CHAR (LMODE)) MODE_HELD, DECODE (REQUEST, 0, ' None ', 1, ' Null ', 2, ' Row-S(SS) ', 3, ' Row-X(SX) ', 4, ' Share', 5, 'S/Row-X (SSX) ', 6, 'Exclusive ', TO_CHAR (REQUEST)) MODE_REQUESTED, O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) ' AS OBJECT_NAME, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2, S2.SQL_TEXT FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S, V$ACCESS A, V$SQL S2 WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND S.SID = A.SID AND S2.HASH_VALUE = S.SQL_HASH_VALUE AND A.OBJECT = 'PROC_VALIDATE_RULE_V3';

效果如下：

转自：http://blog.csdn.net/tianlesoftware/article/details/6822321    在此谢谢DAVE

 

 

ORACLE里锁有以下几种模式:
　　
　　0：none
　　1：null 空
　　2：Row-S 行共享(RS)：共享表锁
　　3：Row-X 行专用(RX)：用于行的修改
　　4：Share 共享锁(S)：阻止其他DML操作
　　5：S/Row-X 共享行专用(SRX)：阻止其他事务操作
　　6：exclusive 专用(X)：独立访问使用
　　
　　数字越大锁级别越高, 影响的操作越多。
　　一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。
　　
　　select ... from ... for update; 是2的锁。
　　
　　当对话使用for update子串打开一个游标时，
　　所有返回集中的数据行都将处于行级(Row-X)独占式锁定，
　　其他对象只能查询这些数据行，不能进行update、delete或select...for update操作。
　　
　　insert / update / delete ... ; 是3的锁。
　　
　　没有commit之前插入同样的一条记录会没有反应,
　　因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
　　
　　创建索引的时候也会产生3,4级别的锁。
　　
　　locked_mode为2,3,4不影响DML(insert,delete,update,select)操作,
　　但DDL(alter,drop等)操作会提示ora-00054错误。
　　
　　有主外键约束时 update / delete ... ; 可能会产生4,5的锁。
　　
　　DDL语句时是6的锁。
　　
　　以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：

　　select object_id,session_id,locked_mode from v$locked_object;

　　select t2.username,t2.sid,t2.serial#,t2.logon_time
　　from v$locked_object t1,v$session t2
　　where t1.session_id=t2.sid order by t2.logon_time;

　　如果有长期出现的一列，可能是没有释放的锁。