求大神帮我看看这两句oracle sql的区别
程序员文章站
2024-03-22 15:00:52
...
最近工作中遇到客户提供和自己产品中Oracle 锁进程指标的sql不一样,我对Oracle也不太懂,希望各位大神赐教!
sql 1
// sql 1
SELECT s.username 用户,
s.machine 终端,
s.client_info IP,
s.osuser 终端用户名,
s.program 进程,
o.object_type 锁类型,
o.object_name 被锁对象,
s.SID || ',' || s.SERIAL# ID,
'alter system disconnect session ''' || s.SID || ',' || s.SERIAL# ||
''' immediate;' 终止进程语句,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) 锁级别,
o.owner,
s.terminal,
a.SQL_TEXT
FROM v$session s,
gv$lock l,
dba_objects o,
v$sqlarea a
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
and l.type = 'TM'
and s.PREV_SQL_ADDR = a.ADDRESS
AND s.username is NOT NULL
order by s.username, s.machine, s.SID || ',' || s.SERIAL#;
sql 2
// sql 2
select s1.INST_ID,
s1.username||'@'||
s1.machine||' (SID='||s1.sid||' ) is blocking '||
s2.username||'@'||
s2.machine||' ( SID='||s2.sid||' ) ' AS blocking_status
from
gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2
where l1.INST_ID=s1.INST_ID
and s1.INST_ID=s2.INST_ID
and s2.INST_ID=l2.INST_ID
and s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;