1 查询trace路径
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest';
2 find对应的trace file,oracle版本不同,查询方法不同
oracle 10g:
select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
from (select p.spid
from v$mystat m, v$session s, v$process p
where m.statistic# = 1
and s.sid = m.sid
and p.addr = s.paddr) p,
(select t.instance
from v$thread t, v$parameter v
where v.name = 'thread'
and (v.value = 0 or t.thread# = to_number(v.value))) i,
(select value from v$parameter where name = 'user_dump_dest') d;
oracle 11g:
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
为了省事,可以设置一个名称
alter session set tracefile_identifier='test';
3 开启trace
SQL> alter session set sql_trace=true
4 执行SQL,然后使用tkprof工具转换trc文件,这样比较好看
tkprof t0fwa_ora_13265_test.trc test.txt