查看session信息的sql

虽然用oem查session比较方便，但有时用sql能比较快获得信息，尤其在连接数已经满的情况下，为了进行诊断，用sql往往能快速找到问题所在。

select
substr(s.username,1,18) username,
substr(s.program,1,15) program,
s.sid,s.serial#,s.machine,s.status,s.logon_time,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command,
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
order by s.status,s.username

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3345/viewspace-924603/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3345/viewspace-924603/