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

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

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。