数据库解锁的sql

最新推荐文章于 2024-05-15 14:45:53 发布

HG_weikaifang

最新推荐文章于 2024-05-15 14:45:53 发布

阅读量340

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/HG_weikaifang/article/details/77884841

数据库专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个Oracle数据库中用于查询锁定信息及关联会话的SQL脚本，并提供了会话杀除命令。该脚本能展示锁类型、模式、相关对象名称等详细信息。

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

select A.sid,
b.SQL_ID,
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','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,
'alter system kill session '''||A.SID||','||b.SERIAL#||''';' as kill,
case when b.SQL_ID is not null then
' select * from v$sqltext where sql_id='''|| b.SQL_ID || ''' order by piece;'
when b.SQL_ID is null then ' ' end
AS showsql

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 ;