--

Tuesday, March 4, 2008

TRACEFILE_IDENTIFIER - Another useful parameter

Many a 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'
/

5 comments:

sonu said...

Hi,

nice note on tracefile_identifier.

I did it in my localsetup and come to know that when I set this parameter it will create the tracefile_identifier.trc file always with "1KB" in size and it will not contain any session id information.

Infact the very next file after the idenfitier will take the session id.

Is it the wright method that I understood?

suggest me..

JazzHarmonicat said...

Don't forget to first:
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';

JazzHarmonicat said...

Don't forget to first:
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';

By the way, the "two words" below are way too hard for humans to read. I've been trying for over 20 times to post.

JazzHarmonicat said...

Don't forget to first:
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';

By the way, the "two words" below are way too hard for humans to read. I've been trying for over 20 times to post.

JazzHarmonicat said...

Don't forget to first:
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';

By the way, the "two words" below are way too hard for humans to read. I've been trying for over 20 times to post.