网上找了一些查询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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21634752/viewspace-691392/