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

9 Comments »

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

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

  2. ariesvilando.soup.io

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

    Trackback by ariesvilando.soup.io — July 17, 2014 @ 2:06 pm

  3. Excellent website you have here but I was curious if you knew of any
    community forums that cover the same topics talked about in this article?

    I’d really love to be a part of group where I can get suggestions from other
    experienced people that share the same interest. If you have any recommendations, please let me know.
    Thanks!

    Comment by Dallas Kitchen and Bathroom Remodeling | Montfort Designs LLC — July 17, 2014 @ 10:41 pm

  4. simply click storify.com

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

    Trackback by simply click storify.com — July 18, 2014 @ 3:30 am

  5. diploma of financial planning

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

    Trackback by diploma of financial planning — July 20, 2014 @ 6:38 am

  6. new ways to make money

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

    Trackback by new ways to make money — July 24, 2014 @ 4:01 am

  7. best places for family vacations

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

    Trackback by best places for family vacations — July 24, 2014 @ 10:59 am

  8. best start up business

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

    Trackback by best start up business — July 25, 2014 @ 5:28 pm

  9. js.Esy.es

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

    Trackback by js.Esy.es — July 28, 2014 @ 9:37 am


RSS feed for comments on this post. TrackBack URI

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

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: