Performance: How to enable Database trace for the Apps session which is already started running

You know how to enable Database trace for Forms & concurrent request, if you know in advance. But if you wish to enable Database trace for the Apps session which already started, then you can use oradebug .

This is required if you wish to do DB tracing when a program suddenly hangs / slow   and you do not want to cancel and resubmit.

Steps to enable DB trace dynamically

1) Identify the sid using below sql.

select sid,program,module from v$session

where program like '%f60web%';

2) Identify the SPID

select spid from 

v$process 

where addr in (

     select paddr 

       from v$session 

      where sid in (&&<from step1>)

                        );

3) Enable trace using ORADEBUG.

sqlplus /nolog

connect / as sysdba

set echo on

ORADEBUG SETOSPID <spidfrom step2>

ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8

4) This enables trace for the running session.

  4.1)  Trace file will be found in the user_dump_dest. 

  4.2)  tkprof tracefile.trc output.txt explain=apps/<passwd> sort=prsela,exeela,fchel

5) If you want to disable , use below command from the same session (step 3)

ORADEBUG EVENT 10046 TRACE NAME CONTEXT off (Rem to turn off; to Switch off the Trace)

Hope this helps.