查看当前oracle中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;