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

38 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

  13. use this link

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

    Trackback by use this link — September 20, 2014 @ 12:44 am

  14. Going at zigzigziggy.co.uk

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

    Trackback by Going at zigzigziggy.co.uk — September 21, 2014 @ 5:43 am

  15. in debt

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

    Trackback by in debt — September 21, 2014 @ 7:48 am

  16. link homepage

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

    Trackback by link homepage — September 21, 2014 @ 2:01 pm

  17. Recommended Reading

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

    Trackback by Recommended Reading — September 21, 2014 @ 2:44 pm

  18. great post to read

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

    Trackback by great post to read — September 21, 2014 @ 4:18 pm

  19. cookstage.com

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

    Trackback by cookstage.com — September 21, 2014 @ 5:10 pm

  20. her response

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

    Trackback by her response — September 21, 2014 @ 5:11 pm

  21. honor.es

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

    Trackback by honor.es — September 23, 2014 @ 2:32 am

  22. micronutrients vitamins and minerals

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

    Trackback by micronutrients vitamins and minerals — September 23, 2014 @ 6:36 am

  23. her latest blog

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

    Trackback by her latest blog — September 23, 2014 @ 8:12 am

  24. sysgen.kr

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

    Trackback by sysgen.kr — September 23, 2014 @ 7:32 pm

  25. next

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

    Trackback by next — September 23, 2014 @ 10:11 pm

  26. visit our website

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

    Trackback by visit our website — September 23, 2014 @ 10:13 pm

  27. freeslotsnodeposit.co.uk

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

    Trackback by freeslotsnodeposit.co.uk — September 24, 2014 @ 1:01 am

  28. click now

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

    Trackback by click now — September 24, 2014 @ 1:42 am

  29. what is data

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

    Trackback by what is data — September 24, 2014 @ 4:31 am

  30. check it out

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

    Trackback by check it out — September 25, 2014 @ 5:20 am

  31. app developer for hire

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

    Trackback by app developer for hire — September 25, 2014 @ 5:51 am

  32. livepressreleasesubmit.com

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

    Trackback by livepressreleasesubmit.com — September 25, 2014 @ 6:26 am

  33. have a peek at this web-site

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

    Trackback by have a peek at this web-site — September 25, 2014 @ 11:31 pm

  34. articlepipelines.com

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

    Trackback by articlepipelines.com — September 26, 2014 @ 8:00 am

  35. 121.182.228.24

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

    Trackback by 121.182.228.24 — September 26, 2014 @ 1:00 pm

  36. click through the up coming website

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

    Trackback by click through the up coming website — September 28, 2014 @ 1:02 pm

  37. isaworld.pe.kr

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

    Trackback by isaworld.pe.kr — September 29, 2014 @ 6:42 am

  38. site web

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

    Trackback by site web — October 9, 2014 @ 2:22 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: