1.获取跟踪文件路径：
  oracle数据库获取trace文件方法：
10g
1.show parameter background_dump_dest
2.show parameter background_dump_dest
11
1.show parameter diag
2.select value from v$diag_info where name = 'Default Trace File';

10g和11g 通用
1.select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest');
获取当前trace文件的脚本：

select c.value || '/' || d.instance_name || '_ora_' ||
a.spid || '.trc' ||
case when e.value is not null then '_'||e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier'
2.如何生成跟踪文件
2.1 跟踪级别：
玩转跟踪不了解跟踪级别是不行的，下面介绍Oracle可采用的跟踪接口：
Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits
2.2 跟踪当前会话：
    alter session set sql_trace=true;
执行相关sql

alter session set sql_trace=false;

  alter session set events '10046 trace name context forever,level 1';
执行相关sql
  alter session set events '10046 trace name context off';
2.3 获取个人的sid，serail#
select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
获取系统级别的会话：
   select s.sid,s.serial#,s.username,s.osuser
  from v$session s,v$process p
    where s.paddr=p.addr;
3.使用oracledebug 跟踪会话

3.1 使用ORADEBUG跟踪当前会话
oradebug只能跟踪SYS用户的当前SESSION，如下方式：
sys@MAA> oradebug setmypid
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12
sys@MAA> exec our code
sys@MAA> oradebug event 10046 trace name context off
sys@MAA> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_17641.trc

3.2 使用ORADEBUG跟踪其他会话
通过oradebug非常方便地跟踪其他会话，如下方式：
luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);
PID SPID
---------- ------------------------------------------------
25 17678
指定跟踪SESSION的SPID(OS process)
sys@MAA> oradebug setospid 17678
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
或者指定跟踪SESSION的PID(Oracle process ID)
sys@MAA> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10053 trace name context forever, level 1
sys@MAA> exec our code
sys@MAA> oradebug event 10053 trace name context off
sys@MAA> oradebug tracefile_name

4.使用autotrace 跟踪当前会话：

  @MAA> set autotrace on
@MAA> set autotrace on explain
  @MAA> set autotrace on statistics
  @MAA> set autotrace traceonly
  @MAA> set autotrace traceonly explain
  @MAA> set autotrace traceonly explain statistics
  @MAA> set autotrace off
  我喜欢使用缩略方式，比如
@MAA> set autot trace exp stat

5.使用10053跟踪会话：

MAA> alter session set events '10053 trace name context forever, level 1';
MAA> alter session set events '10053 trace name context off';
MAA> oradebug event 10053 trace name context forever, level 1
MAA> oradebug event 10053 trace name context off

ITPub博客

一步一步教你学习如何玩转跟踪文件

最新文章