欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

求大神帮我看看这两句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;