Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: TRACEFILE_IDENTIFIER helpful in tracing sessions.

  1. #1
    Oracle Administrator
    Join Date
    Oct 2011

    TRACEFILE_IDENTIFIER helpful in tracing sessions.

    - Many a times you need to enable tracing at a session level.

    - When you do that, you have to make a little bit of effort in digging out your session's trace file. 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".

    - 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.

    Here is how to use this?

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

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

    3. Ask developer session trace is on for your requested session id execute your queries/statements and when developer notifies then

    4. Turn-off tracing
    sql> alter session set events '10046 trace name context off';
    sql> alter session set sql_trace=false;

    5. Check the user_dump_dest folder and you will find a file name "SID_ora_nnnn_pkumar_sql_trace.trc

    6. User can convert the .trc to there readable format by using TKPROF <.trc file name> <.txt conversion file name> and there are several other
    parameters for using with TKPROF like explain plan, sort etc. I will post these in separate thread.

    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 = 'user_dump_dest'
    Cheers !

  2. #2
    TKPROF Utility in Oracle:---

    TKPROF Utility is not a tuning utility is just an utility to read the trace file. So, one time we have the trace file of one session we can use the TKPROF to read the trace file and to see what happen in that session.

    Here are the steps in using TKPROF utility:---

    1. Enable Oracle database to gather statistics on a) Session Level b) System Level

    a)Session Level

    Alter session set timed_statistics = true;

    b) System Level

    Alter System set timed_statistics = true;

    2. Find the SID, SERIAL# for a specific session you want to monitor :-

    SELECT username, sid, serial#, program FROM v$session WHERE username = <usr_nm>;

    3. Enable the tracing for this session ( must be logged as SYSDBA )

    At Session Level:--



    At System Level or DBA may enable tracing for another user’s session by:--

    EXEC dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true);

    4. Identify the directory where the trace file is generated:-

    SELECT value FROM v$parameter WHERE name='user_dump_dest';

    $show parameter user_dump_dest

    5. Identify the name of the trace file which is generated

    SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid
    FROM v$session s, v$process p
    WHERE p.addr = s.paddr and s.username = <User_Name>;

    The name of the trace file is <oracle_sid>_ora_<p.spid>.trc

    6. CONVERT the trace file into a readable format

    cd $ORACLE_HOME/admin/<oracle_sid>/udump

    $tkprof <name of the trace file> <readable.txt>

    7. Disable the tracing for this session ( must be logged as SYSDBA )

    EXEC dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);
    Last edited by vineettyagi; 03-01-2012 at 12:52 PM.

+ Reply to Thread

Similar Threads

  1. Replies: 1
    Last Post: 11-13-2013, 09:50 AM
  2. Script to Monitor datapump sessions
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 07-15-2012, 07:53 PM
  3. Sessions sorted by logon time
    By devesh_ocp in forum Database Scripts
    Replies: 0
    Last Post: 04-24-2012, 10:39 PM
  4. Script to get active middle-tier user sessions
    By Amit in forum SCRIPTS : Users,Profiles,Applications,Responsibilities,Forms,JDBC,Patch,Versions,Products
    Replies: 0
    Last Post: 11-04-2011, 09:37 AM
  5. Check Database Long Runing Sessions
    By Amit in forum Database Scripts
    Replies: 0
    Last Post: 11-03-2011, 12:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics