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;

?

?