oracle查询一些session相关的信息

JoeJJ

1 获取当前session的sid
 
       select sid, serial# from v$mystat where rownum=1

2  获取sid和spid
  （1）根据sid获取spid
         select spid from v$process p, v$session s where s.sid=&sid and s.paddr=p.addr;
 
 
   (2) 获取当前session的sid和spid
          select sid, spid from v$process p, v$session s  where s.paddr=p.addr and s.sid=(select sid from v$mystat where rownum=1);
 
3 获取当前session的trace文件路径
 
select p.value || '/' || t.instance || '_ora_' || ltrim(to_char(p.spid,'fm99999')) || '.trc'  trace_path
 from v$process p, v$session s, v$parameter p, v$thread t
 where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1) and p.name = 'user_dump_dest';
 
或者
select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc' trace_file_name
  from (select p.spid
          from sys.v$mystat m, sys.v$session s, sys.v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr) p,
       (select t.instance
          from sys.v$thread t, sys.v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from sys.v$parameter where name = 'user_dump_dest') d;

 
4 根据sid查看当前正在执行或最近一次执行的语句
 
  select /*+ ordered */ sql_text from v$sqltext sql
  where (sql.hash_value, sql.address) in (
    select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)
    from v$session s where s.sid=&sid)
  order by piece asc;