oracle查会话执行情况,查看当前oracle中session中正在执行的SQL

1、查看全部session中SQL:

spool d:\oracle_realtime_sqllist.txt

SET LINESIZE 100

SET PAGESIZE 50000

COL USERNAME FOR A10

COL machine FOR A15

COL sql_text FOR A50

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.serial#,s.username,s.status

;

spool off

2、查看某个时，检索条件中指定s.sid值即可。

SET LINESIZE 100

SET PAGESIZE 50000

COL USERNAME FOR A10

COL machine FOR A15

COL sql_text FOR A50

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=&sid

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

;

3、根据操作系统进程号查看某个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,v$process z

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.paddr = z.addr

and z.spid = &pid

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

;

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