Description:
Question: How will you find your own TRACE File (say
ORA_5310.trc),
while, multiple Trace files are generated
simultaneously using Same USERNAMEs with Multiple SESSIONs in Oracle
and even when user has given name to trace file?

Code:

    Answer:
    =======
    To find the Trace file Sequence Name:
    ---------------------------------------------------------------------
     By Default oracle uses the format of {instance_name}_ora_{spid from v$process}[_{tracefile_identifier}].trc
     The last portion of [_{tracefile_identifier}] is added only when this value is set either in session level
     or system level.

    To find the tracefile Location:
    -----------------------------------------------------
    The trace file is located based on value of
    user_dump_dest parameter in init.ora file.
    and its View name is v$parameter.

    To set the session identifier:
    -----------------------------------------------------
    You can name the tracefile which is helpful in identifying it at udump location.
    ALTER SESSION SET TRACEFILE_IDENTIFIER=tkprof;
    SQL> show parameter TRACEFILE_IDENTIFIER;

    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- ------------------------------
    tracefile_identifier                 string                           TKPROF

    Now trace file will use the format {instance_name}_ora_{spid from v$process}_{tracefile_identifier}.trc

    SCRIPT CODE:
    **************

    ---------------Script Starts here-------------

   set linesize 100
   COLUMN "YOUR TRACEFILE WITH PATH IS:" FORMAT A80

   SELECT a.trace_path || '> ' || b.trace_file "YOUR TRACEFILE WITH PATH IS:"
     FROM (SELECT VALUE trace_path
             FROM v$parameter
            WHERE NAME = 'user_dump_dest') a,
          (SELECT    'ORA_' || spid
                  || (SELECT NVL2(VALUE, '_' || VALUE, NULL) AS VALUE
                        FROM v$parameter
                       WHERE NAME = 'tracefile_identifier')
                  || '.trc' trace_file
             FROM v$process, v$session
         WHERE addr = paddr AND audsid = USERENV('SESSIONID')) b;

    ---------------Script Ends here-----------------



    Sample Output:
    =============
    
    
    YOUR TRACEFILE WITH PATH IS:
    --------------------------------------------------------------------------------
    /u01/app/oracle/admin/WMBNCH/udump> ORA_651560.trc

    SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER=tkprof;

    

    YOUR TRACEFILE WITH PATH IS:
    --------------------------------------------------------------------------------
    /u01/app/oracle/admin/WMBNCH/udump> ORA_651560_TKPROF.trc