ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看当前oracle中session中正在执行的SQL

查看当前oracle中session中正在执行的SQL

原创 Linux操作系统 作者:chijiao 时间:2012-04-13 11:03:47 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORA-01031处理
请登录后发表评论 登录
全部评论

注册时间:2010-06-18

  • 博文量
    14
  • 访问量
    48147