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;