查看当前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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/83911/viewspace-721071/