Oracle中查询锁的数量,该如何处理
日期：2014-05-17 浏览次数：21059 次
Oracle中查询锁的数量
RT
如何使用SQL语句查询
------解决方案--------------------
查询锁定的表:
select s.audsid AUDSID,
s.SID,
s.SERIAL#,
l.os_user_name "OS Username",
l.oracle_username "Username",
l.process "Process",
p.spid "SPID",--进程ID
o.owner "Owner",
o.object_name "Object",
decode(l.locked_mode, 1,'No Lock', 2,'Row Share', 3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) "Lock Mode",
substr(s.status,1,8) "Status"
from v$locked_object l, all_objects o, v$session s, v$process p
where l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and s.status <> 'KILLED';
kill session:
alter system kill session 'sid,serial#';
kill process:
KILL -9 'SPID'
------解决方案--------------------
RT
如何使用SQL语句查询
------解决方案--------------------
查询锁定的表:
select s.audsid AUDSID,
s.SID,
s.SERIAL#,
l.os_user_name "OS Username",
l.oracle_username "Username",
l.process "Process",
p.spid "SPID",--进程ID
o.owner "Owner",
o.object_name "Object",
decode(l.locked_mode, 1,'No Lock', 2,'Row Share', 3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) "Lock Mode",
substr(s.status,1,8) "Status"
from v$locked_object l, all_objects o, v$session s, v$process p
where l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and s.status <> 'KILLED';
kill session:
alter system kill session 'sid,serial#';
kill process:
KILL -9 'SPID'
------解决方案--------------------
- SQL code
--當前會話鎖的數量 select count(*) from v$session s,v$lock l where s.sid=l.sid;
------解决方案--------------------
1. 查看被锁的表
SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,
b.os_user_name
FROM v$process p, v$session a, v$locked_object b, all_objects c
WHERE p.addr = a.paddr AND a.process = b.process
AND c.object_id = b.object_id
2. 查看是哪个进程锁的
SELECT sid, serial#, username, osuser FROM v$session where osuser = 'tangpj'
2. 查看是哪个进程锁的
SELECT sid, serial#, username, osuser,'ALTER SYSTEM KILL SESSION '''||SID||','|| SERIAL#||''';'
FROM v$session where osuser = 'Administrator'
3. 杀掉这个进程 alter system kill session 'sid,serial#';
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
