查看当前oracle中session中正在执行的SQL
1.查看全部session中SQL
/* Formatted on 2013/8/5 10:01:05 (QP5 v5.114.809.3010) */
spool /home/oracle/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.查看某个session时，检索条件中指定s.sid值即可
/* Formatted on 2013/8/5 10:02:27 (QP5 v5.114.809.3010) */
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
/* Formatted on 2013/8/5 10:04:57 (QP5 v5.114.809.3010) */
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;
