查询oracle正在执行的sql以及session

发布时间：2020-12-12 14:56:59 所属栏目：百科来源：网络整理

导读：select substr(s.username,1,18) username, s.sid,s.serial#,s.machine,y.sql_text from v$session s,v$process p,v$transaction t,v$rollstat r,v$rollname n,v$sql y where s.paddr = p.addr and s.taddr = t.addr (+) and t.xidusn = r.usn (+) and r.usn

select
substr(s.username,1,18) username,
s.sid,s.serial#,s.machine,y.sql_text
from v$session s,v$process p,v$transaction t,v$rollstat r,v$rollname n,v$sql y
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
and s.username is not null
and s.sql_address=y.address
--and s.sid=56

order by s.sid,s.username,s.status

查询oracle正在执行的资源

SELECT 'Lock' "Status",a.username "用户名",a.sid "SID",a.serial# "SERIAL#",b.type "锁类型",DECODE(b.lmode,'No Lock',2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive','NONE') "占用的模式",DECODE(b.request,'NONE') "请求的模式",c.object_name "对象名",c.owner "对象所有者",c.object_type "对象类型",b.id1 "资源ID1",b.id2 "资源ID2",b.ctime "ctime(秒) ",'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.serial#||''';' "kill Session ",'kill -9 '||d.spid "Kill Process (Unix Linux)",'orakill '||f.instance_name||' '||d.spid "Kill Process (Windows)" FROM v$session a,v$lock b,v$locked_object b1,dba_objects c,v$process d,v$instance f WHERE a.type <> 'BACKGROUND' AND a.sid = b.sid AND b.request = 0 AND d.addr = a.paddr AND b1.session_id = a.sid AND b1.object_id = c.object_id AND f.status = 'OPEN' AND f.database_status = 'ACTIVE' order by b.ctime

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!