Jagjeet's Oracle Blog!

October 3, 2009

How to identify tracefile in 11g

Filed under: Oracle 11g — Tags: , — Jagjeet Singh @ 11:36 am


It’s quite often that DBA needs to enable tracing for a particular session to diagnose the performance or any other issue, which create trace file(s) in user_dump_directory. Usually this directory contains hundreds of files on a medium/large user base system, DBA needs to identify the OS process id to identify the trace file.


Naming Convention of user trace file is <<instance_name>>_ora_<<process id>>.trc under user_dump_directory. Oracle also provide option to use tracefile_identifire which can be utilized to make file name unique to identify easily.


Tracefile identifire will be appended in the name of file.In 11g, It has become very easy to identify the trace file directly from v$process view. Column named “Tracefile” has been added to this view.


SQL> select tracefile from v$process join v$session on (addr=paddr)
2 where sid = 76;

TRACEFILE
——————————————————————-
/u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817.trc

SID 76 is my session id.

After setting Tracefile Identifier.

SQL> alter session set tracefile_identifier='JS';

Session altered.

SQL> select tracefile from v$process join v$session on (addr=paddr)
2 where sys_context(‘userenv’,’sessionid’) = audsid
3 /

TRACEFILE
——————————————————————-
/u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc


Note – I have removed SID and using sys_context… function to identify the current session. SID is useful to identify end-user’s sessions.


This column will always be populated with name of trace file even there has been no file generated.

SQL> ! ls -l /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc
ls: /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc: No such file or directory


After enabling trace.

SQL> alter session set sql_trace=true;

Session altered.

SQL> !ls -l /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc
-rw-r----- 1 oracle dba 1101 Oct 3 07:10 /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc


before 11g, below query can be utilized to identify the trace file.


SQL> select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid
|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');

About these ads

12 Comments

  1. Awesome Sir. You are really in love with Oracle,,, Goldy

    Comment by Goldy — November 28, 2012 @ 6:20 pm

  2. rina-karl.com

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by rina-karl.com — August 8, 2014 @ 10:34 pm

  3. unicron13.weebly.com

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by unicron13.weebly.com — August 19, 2014 @ 12:59 am

  4. This Web-site

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by This Web-site — August 19, 2014 @ 1:51 am

  5. click through the following document

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by click through the following document — August 20, 2014 @ 6:58 am

  6. emerging multimedia technologies

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by emerging multimedia technologies — August 23, 2014 @ 6:44 am

  7. financial planning cfp

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by financial planning cfp — August 24, 2014 @ 11:20 pm

  8. rising interest rates

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by rising interest rates — August 30, 2014 @ 11:08 pm

  9. please click the up coming website page

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by please click the up coming website page — September 2, 2014 @ 9:11 pm

  10. mouse click the up coming post

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by mouse click the up coming post — September 5, 2014 @ 7:34 am

  11. click through the following web page

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by click through the following web page — September 5, 2014 @ 7:34 am

  12. hamilton steel companies

    How to identify tracefile in 11g | Jagjeet’s Oracle Blog!

    Trackback by hamilton steel companies — September 8, 2014 @ 10:30 pm


RSS feed for comments on this post. TrackBack URI

Sorry, the comment form is closed at this time.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: