v$lock
V$LOCK lists the locks currently held by the Oracle Database and outstanding(未完成的) requests for a lock or latch.
ADDR - Address of lock state object
KADDR - Address of lock
SID - Identifier for session holding or acquiring the lock
TYPE - Type of user or system lock
ID1 - Lock identifier #1 (depends on type)
ID2 - Lock identifier #2 (depends on type)
LMODE - Lock mode in which the session holds the lock
REQUEST - Lock mode in which the process requests the lock
CTIME - Time since current mode was granted
BLOCK - A value of either 0 or 1, depending on whether or not the lock in question is the blocker.
个别字段说明:
SID : 持有锁或等待锁的会话id
TYPE : 锁的类型, 其中用户类型的锁分为:TX(行锁) TM(表锁) UL(User supplied)
ID1 : 若type=TM, id1为对象的id, ID2=0;
若type=TX,将id1转化为0xUUUUSSSS形式,则 UUUU对应v$transaction中的XIDUSN,SSSS对应v$transaction总的XIDSLOT, ID2对应v$TRANSACTION中的XIDSQN
LMODE : 会话持有的锁模式
REQUEST : 进程请求的锁模式
BLOCK : 为1代表阻碍者,表示正在阻碍其它会话
解决问题:某些会话莫名其妙的被阻塞了,定位到底是被谁阻塞了?
1. 执行如下语句
select t.OSUSER,t.PROCESS,t.MACHINE,t.PROGRAM,t.USER#,t.USERNAME,t.SID,t.SERIAL#,t1.SQL_TEXT,t.BLOCKING_INSTANCE,t.BLOCKING_SESSION,t.EVENT "cause"
from v$session t,v$sqltext t1
where t.SQL_ADDRESS=t1.ADDRESS
and t.SQL_HASH_VALUE=t1.HASH_VALUE
and t.LOCKWAIT is not null
and t.BLOCKING_SESSION_STATUS='VALID';
根据上面的cause列以及blocking_instance blocking_session再去定位blocking_session,要和发起blocking_session的用户商量,是否对应的session不再用,若不再用,则kill 掉
2. kill 掉 blocking session
alter system kill session 'blocking_sid,blocking_serial#'; --这里要注意了,不要把系统的session kill掉
小技巧:
--查看当前会话的sid
select userenv('sid') from dual;
--查看正在阻塞其他会话的会话
SQL> select * from v$lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------ ---- ----------- ---------- ---------- ---------- ---------- ----------
44AC5434 44AC5550 18 TX 655380 128 6 0 144 1