----------
129
----------
128
---------- ----------------
128 129
SQL> select sid,blocking_session ,event from v$session where blocking_session is not null;
---------- ---------------- ------------------------------
128 129 enq: TX - row lock contention
select * from v$locked_object;
select dbo.* from v$locked_object lo ,dba_objects dbo where lo.object_id = dbo.object_id and lo.xidusn=0
通过查询v$lock可以看到是哪一个session阻塞了哪一个session:142阻塞了139
with blkedsess as (select * from v$lock where request !=0)
select blkingsess.sid blockingsid, blkedsess.sid blockedsid
from v$lock blkingsess,blkedsess
where blkingsess.id1 = blkedsess.id1
and blkingsess.id2 = blkedsess.id2
and blkingsess.sid != blkedsess.sid
在通过v$session可以查到session相关的信息,被阻塞的status一般为ACTIVE,还可以通过sql_address联合v$sql找到被阻塞的SQL语句.
select sid,serial#,status,sql_address from v$session where sid in(139,142)
select * from V$sql where address='6BE7D33C'
or
select sql_text, sql_fulltext, sql_id from v$sqlarea where sql_id='6BE7D33C';
or
select sql_text from v$sqltext where sql_id = '6BE7D33C';
这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session
alter system kill session '142,7'; 其中142为sid,7为serial#