ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于session 的一些sql语句

关于session 的一些sql语句

原创 Linux操作系统 作者:wuft2003 时间:2011-03-31 15:23:16 0 删除 编辑

网上找了一些查询sql session的语句

--sid, serial#, audsid,saddr,paddr,sql_id,status
from v$session
where suser = 'oracle'
and sid = '516'

alter system kill session '516,32081'
--- 查询会话统计信息
select   a.sid,a.statistic#,b.name,a.value
from   v$sesstat   a,v$statname   b
where   a.statistic#   =   b.statistic#
and   a.sid   =   &sid;

--- 查询当前会话的状态
  SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#

  FROM V$process p, V$session s

  WHERE s.paddr ='07000000ACC45248' and s.Paddr = p.Addr ;
      
select * from v$session_wait where sid = &sid ;
---- 查询session的OS进程ID

  SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,

  s.Osuser, s.Machine

  FROM V$process p, V$session s, V$bgprocess b

  WHERE p.Addr = s.Paddr

  AND p.Addr = b.Paddr

  And (s.sid=&1 or p.spid=&1)

  UNION ALL

  SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,

  s.Serial#, s.Osuser, s.Machine

  FROM V$process p, V$session s

  WHERE p.Addr = s.Paddr

  And (s.sid=&1 or p.spid=&1)

  AND s.Username IS NOT NULL;

---根据sid查看对应连接正在运行的sql

  SELECT /*+ PUSH_SUBQ */

  Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,

  Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,

  Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,

  Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,

  SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status

  FROM V$sqlarea

  WHERE Address = (SELECT Sql_Address

  FROM V$session

  WHERE Sid = &sid );

---求当前session的跟踪文件或者指定session

  SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename

  FROM V$process p, V$session s, V$parameter P1, V$parameter P2

  WHERE P1.NAME = 'user_dump_dest'

  AND P2.NAME = 'instance_name'

  AND p.Addr = s.Paddr

  AND s.Audsid = '4294967295' ---Userenv('SESSIONID')

  AND p.Background IS NULL

  AND Instr(p.Program, 'CJQ') = 0;

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

下一篇: 修改unixware 时间
请登录后发表评论 登录
全部评论

注册时间:2009-05-12

  • 博文量
    295
  • 访问量
    332874