ORACLE LOCK脚本.txt
免费下载
查看锁等待
set line 1000
col oracle_username for a20
col object_name for a20
col locked_modefor 99
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
查询 SQL 语句
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
SELECT DISTINCT
s1.username
|| '@'
|| s1.machine
|| ' ( INST='
|| s1.inst_id
|| ' SID='
|| s1.sid
|| ' Serail#='
|| s1.serial#
|| ' ) IS BLOCKING '
|| s2.username
|| '@'
|| s2.machine
|| ' ( INST='
|| s2.inst_id
|| ' SID='
|| s2.sid
|| ' Serial#='
|| s2.serial#
|| ' ) '
AS blocking_status
FROM gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2 ,
dba_objects ao,
v$locked_object lo
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND s1.inst_id = l1.inst_id
AND s2.inst_id = l2.inst_id
AND l1.block > 0
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2
and ao.object_id = lo.object_id and ao.object_name=upper('test')
;
set line 1000
col
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null;
set line 1000
col object_name for a40
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid and
ao.object_name=upper('test');
SELECT
C.SID
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS) and a.object_name='&tb'
ORDER BY 1, 2;
set line 1000
select a.inst_id,
c.username,
a.sid,
c.serial#,
c.program,
' is blocking ',
b.inst_id,
d.username,
b.sid,
d.serial#,
d.program
from (select inst_id, sid, id1, id2 from gv$lock where block >0) a,
(select inst_id, sid, id1, id2 from gv$lock where request > 0) b,
(select inst_id,sid, serial#, username,osuser, terminal, program
from gv$session) c,
(select inst_id,sid,
serial#,
username,
program from gv$session) d
where a.id1 = b.id1
and a.id2 = b.id2
of 14
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论