oracle 被锁,解锁,阻塞语句-CSDN博客

本文提供了一种查询锁定表的方法，并展示了如何使用SQL语句获取锁定信息及解锁被阻塞的会话。通过查询v$lock、v$session等视图，可以定位到导致阻塞的具体SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//查询被锁的表
select A.sid, b.serial#,
decode(A.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
c.object_name,
b.username,
b.osuser,
decode(a.lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
B.MACHINE,D.SPID
from v$lock a,v$session b,all_objects c,V$PROCESS D
where a.sid=b.sid and a.type in ('TM','TX')
and c.object_id=a.id1
AND B.PADDR=D.ADDR

//用于解锁
alter system kill session 'SID,SERIAL#'

//获得引起阻塞得源头语句(先需要获得sid然后查询，如160)
select sql_text from v$sqlarea
where (v$sqlarea.address, v$sqlarea.hash_value) in (
select sql_address, sql_hash_value
from v$session
where sid in(
select sid from v$session a,x$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnmod <> 0
and b.kglpnmod in (select p1raw from v$session_wait where sid=170 and event like 'library%')
)
)