Oracle跟踪文件

1.跟踪文件分类

   1）计划内的、由用户请求所产生的跟踪文件

   2）计划外的、数据库服务器自动产生的跟踪文件

2.计划内的、由用户请求所产生的跟踪文件

   2.1 生成

①alter session set sql_trace=true

②DBMS_MONOTOR.SESSION_TRACE_ENABLE

③10046事件：alter session set events '10046 trace name context forever,level 12'

   2.2 跟踪文件位置

①

SQL> show parameter dump_dest

NAME                 TYPE     VALUE
------------------------------ ----------- ------------------------------
background_dump_dest     string /oracle/diag/rdbms/ora11g/ORA11G/trace
core_dump_dest      string     /oracle/diag/rdbms/ora11g/ORA11G/cdump
user_dump_dest     string     /oracle/diag/rdbms/ora11g/ORA11G/trace

②

SQL> col name format a30
SQL> col value format a50
SQL> select name,value
  2  from v$parameter
  3  where name like '%dump_dest%';

NAME                   VALUE
------------------------------ --------------------------------------------------
background_dump_dest           /oracle/diag/rdbms/ora11g/ORA11G/trace
user_dump_dest        /oracle/diag/rdbms/ora11g/ORA11G/trace
core_dump_dest           /oracle/diag/rdbms/ora11g/ORA11G/cdump

③

SQL> select name,value from v$diag_info;

NAME                   VALUE
------------------------------ --------------------------------------------------
Diag Enabled               TRUE
ADR Base               /oracle
ADR Home               /oracle/diag/rdbms/ora11g/ORA11G
Diag Trace               /oracle/diag/rdbms/ora11g/ORA11G/trace
Diag Alert               /oracle/diag/rdbms/ora11g/ORA11G/alert
Diag Incident               /oracle/diag/rdbms/ora11g/ORA11G/incident
Diag Cdump               /oracle/diag/rdbms/ora11g/ORA11G/cdump
Health Monitor               /oracle/diag/rdbms/ora11g/ORA11G/hm
Default Trace File           /oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_32984_look_for_me.trc

Active Problem Count           0
Active Incident Count           0

11 rows selected.

   2.3得到当前会话跟踪文件

①v$diag_info(Default Trace File)(>=11g)

②11g之前通过查询视图得到

SQL> col trace new_val TRACE format a100
SELECT C.VALUE || '/' || D.INSTANCE_NAME || '_ora_' || a.spid || '.trc'  trace
  FROM v$process a,
       v$session b,
       v$parameter c,
       v$instance d
WHERE     A.ADDR = B.PADDR
       AND B.AUDSID = USERENV ('sessionid')
  8         AND c.name = 'user_dump_dest';

TRACE
--------------------------------------------------------------------------------
/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_33355.trc

SQL> !ls &TRACE
ls: cannot access /oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_33355.trc: No such file or directory

--在启用跟踪之前，跟踪文件并不存在

---启用跟踪

SQL> exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

SQL> !ls &TRACE
/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_33355.trc

--给跟踪文件加标识符
SQL> alter session set tracefile_identifier='Look_For_Me';

Session altered.

SQL> !ls /oracle/diag/rdbms/ora11g/ORA11G/trace/*Look_For_Me*
/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_33355_Look_For_Me.trc  /oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_33355_Look_For_Me.trm

.trc是跟踪文件

.trm是跟踪文件的图文件（trace mat file）

--禁用跟踪

SQL> exec dbms_monitor.session_trace_disable

PL/SQL procedure successfully completed.

3.计划外的、数据库服务器自动产生的跟踪文件

   由Oracle自动生成，不在计划之内。

   ADRCI工具