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

一步一步教你學習如何玩轉跟蹤檔案

相關文章