Monday, November 19, 2012

Which trace file is mine?

A common issue when tracing is finding your trace file after the tracing is done.  Back in the bad old days I would do something like "select 'Ric Van Dyke' from dual;" in the session I was tracing.  Once I was finished I'd then do a search for a trace file with "Ric Van Dyke" in the file to find mine.  It worked, but today we have a much more elegant way to find a trace file:

SQL> alter session set tracefile_identifier='MY_TRACE';

SQL> exec dbms_monitor.session_trace_enable(null,null,true,true,'ALL_EXECUTIONS');

SQL> select 'hello' from dual;

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\app\hotsos\diag\rdbms\hotsos\hotsos\trace>dir *MY_TRACE*
11/15/2012  04:30 PM            10,220 hotsos_ora_5540_MY_TRACE.trc
11/15/2012  04:30 PM                94 hotsos_ora_5540_MY_TRACE.trm
               2 File(s)         10,314 bytes

You can make this more elaborate for example this is an anomomys PL/SQL block I have used in the past to turn on tracing, which dynamicly sets TRACEFILE_IDENTIFIER to the current time and user:

  t_trc_file varchar2(256):= 
    'alter session set tracefile_identifier='||chr(39)||
    to_char(sysdate, 'hh24miss’)||'_'||user||chr(39);
  t_trc_stat varchar2(256):=
     'alter session set timed_statistics=true';
  t_trc_size varchar2(256):=
    'alter session set max_dump_file_size=unlimited';
  t_trc_sql  varchar2(256):=
    'alter session set events '||chr(39)||
    '10046 trace name context forever, level 12'||chr(39);
    execute immediate t_trc_file;  -- Set tracefile identifier
    execute immediate t_trc_stat;  -- Turn on timed statistics
    execute immediate t_trc_size;  -- Set max Dump file size
    execute immediate t_trc_sql;   -- Turn on Trace

1 comment:

  1. Very nice Ric - chr(39) is much easier (and easier to read as well) than all the crazy quotes. I will use this forever.