Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

TRACEFILE_IDENTIFIER – Another useful parameter

TRACEFILE_IDENTIFIER – Another useful parameter

Many  times you need to enable tracing at a session level. And when you do that, you have to make a little bit of effort in digging out your session’s trace file. Why? because the trace file is created in the user_dump_dest directory and there would be so many other trace files, and all of them would have similar naming convention “SID_ora_nnnn.trc”. However with the help of the parameter “TRACEFILE_IDENTIFIER”, you can easily distinguish your trace file. Very useful specifically when you want to use trace analyzer etc.

Here is how?

1. Set the tracefile identifier as you want, this will be appended to trace file name.
alter session set tracefile_identifier=’sipras_sql_trace’;

2. Enable your session level tracing
alter session set sql_trace=true;
alter session set events ‘10046 trace name context forever, level 12’ ;

3. Execute your queries/statements

4. Turn-off tracing
alter session set events ‘10046 trace name context off’;
alter session set sql_trace=false;

5. Check the user_dump_dest folder and you will find a file name “SID_ora_nnnn_sipras_sql_trace.trc

See now it’s so easy to identify. Having said that you can still find out the trace file name without using TRACEFILE_IDENTIFIER parameter using the following SQL but when Oracle has provided an easier method, why not use it?

— sql to find out trace file name for your session.

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’)
and c.name = ‘user_dump_dest’
/

March 20, 2008 - Posted by | oracle

1 Comment »

  1. Fantastic site. Lots of useful information here. I am sending it to a few pals ans also sharing in delicious.
    And obviously, thank you for your sweat!

    Comment by Wyatt | September 17, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: