ORACLE LOCK

V$LOCK
ADDR 锁定状态对象地址
KADDR 锁地址
SID 会话id
ID1 锁标识符#1
ID2 锁标识符#2
LMODE 会话持有的锁模式（0~6）
REQUEST 进程请求的锁模式（0~6）
CTIME 当前模式的时间

BLOCK 为1代表阻碍者，表示正在阻碍其它会话

view plain

1. --查找正在阻碍其它会话（阻碍者）的会话--sid为138的会话正在阻碍其它的会话
2. SQL>SELECTt.ADDR,
3. 2t.KADDR,
4. 3t.SID,
5. 4t.TYPE,
6. 5t.ID1,
7. 6t.ID2,
8. 7t.LMODE,
9. 8t.REQUEST,
10. 9t.CTIME,
11. 10t.BLOCK
12. 11FROMv$lockt
13. 12WHEREt.BLOCK=1
14. 13ORDERBYt.CTIMEDESC;
16. ADDRKADDRSIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK
17. ------------------------------------------------------------------------------------------
18. 289E1F90289E1FA8138TM5143406010921
19. 28A4917C28A49298138TX393262353605381
21. SQL>
22. --查找正在阻碍其它会话（阻碍者）的会话--sid为138会话的详细信息
23. SQL>SELECTt1.SID,
24. 2t1.SERIAL#,
25. 3t1.USERNAME,
26. 4t2.TYPE,
27. 5t2.ID1,
28. 6t2.ID2,
29. 7t2.LMODE,
30. 8t2.REQUEST,
31. 9t2.CTIME,
32. 10t2.BLOCK
33. 11FROMv$sessiont1,v$lockt2
34. 12WHEREt1.SID=t2.SID
35. 13ANDt1.USERNAMEISNOTNULL--USERNAME为NULL代表oracle后台进程
36. 14ANDt2.BLOCK=1
37. 15ORDERBYt2.CTIMEDESC;
39. SIDSERIAL#USERNAMETYPEID1ID2LMODEREQUESTCTIMEBLOCK
40. ------------------------------------------------------------------------------------------------------------------
41. 1382SYSTM5143406011221
42. 1382SYSTX393262353605681
46. --如果锁类型为(TYPE)为TM，v$lock.ID1代表锁定的对象id(dba_objects.OBJECT_ID).
47. SQL>SELECTt.owner,t.object_nameFROMdba_objectstWHEREt.object_id=51434;
49. OWNEROBJECT_NAME
50. --------------------------------------------------------------------------------------------------------------
51. SYST_LOCK
53. /**如果锁类型为(TYPE)为TX：
54. *v$lock.ID1代表v$transaction.XIDUSN和v$transaction.XIDSLOT，(ID1的高16位为XIDUSN，低16位为XIDSLOT)。
55. *v$lock.ID2为v$transaction.XIDSQN。
56. *
57. *以下是ID1,ID2与v$transaction列的转换
58. */
59. SQL>SELECTs.XIDUSN,s.XIDSLOT,s.XIDSQNFROMv$transactionsWHEREs.XIDSQN=353;
61. XIDUSNXIDSLOTXIDSQN
62. ------------------------------
63. 646353
64. --ID1转换为v$transaction.XIDUSN和v$transaction.XIDSLOT，正好与上面sql相同结果
65. SQL>SELECTtrunc(393262/power(2,16))XIDUSN,
66. 2bitand(393262,to_number('ffff','xxxx'))+0XIDSLOT
67. 3FROMdual;
69. XIDUSNXIDSLOT
70. --------------------
71. 646
73. SQL>
75. --查找被阻塞（正在等待）会话执行的sql，
76. SQL>SELECTt1.SID,
77. 2t1.SERIAL#,
78. 3t1.USERNAME,
79. 4t2.CTIME,
80. 5t2.TYPE,
81. 6t2.REQUEST,
82. 7t3.PIECE,
83. 8t3.SQL_TEXT
84. 9FROMv$sessiont1,v$lockt2,v$sqltextt3
85. 10WHEREt1.SID=t2.SID
86. 11ANDt1.USERNAMEISNOTNULL
87. 12ANDt1.LOCKWAIT=t2.KADDR
88. 13--ANDt3.SQL_ID=t1.SQL_ID
89. 14ANDt3.ADDRESS=t1.SQL_ADDRESS
90. 15ANDt3.HASH_VALUe=t1.SQL_HASH_VALUE
91. 16ORDERBYt2.CTIMEDESC,t3.PIECE;
93. SIDSERIAL#USERNAMECTIMETYPEREQUESTPIECESQL_TEXT
94. ---------------------------------------------------------------------------------------------------------------------------
95. 13152SYS3499TM40locktablet_lockinsharemode
96. 15028SYS3036TX60updatetsettext='jerry'whereid=1
98. --查看阻碍者阻塞了哪些会话
99. SQL>SELECTt1.SID,
100. 2t1.USERNAME,
101. 3t1.BLOCKING_SESSION,
102. 4t1.BLOCKING_SESSION_STATUS
103. 5FROMv$sessiont1
104. 6WHEREt1.LOCKWAITISNOTNULL
105. 7ANDt1.BLOCKING_SESSION=138
106. 8--ANDt1.USERNAMEISNOTNULL
107. 9ORDERBYt1.SID;
109. SIDUSERNAMEBLOCKING_SESSIONBLOCKING_SESSION_STATUS
110. -------------------------------------------------------------------------------
111. 131SYS138VALID--ACTIVE-SessioncurrentlyexecutingSQL
112. 150SYS138VALID
114. --kill会话，当确定了会话之后，可以kill，对于oracle进程要慎重
115. SQL>altersystemkillsession'131,52';
117. Systemaltered