Enable Database trace for the Apps session

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 (&&));


3) Enable trace using ORADEBUG.

sqlplus /nolog
connect / as sysdba
set echo on
ORADEBUG SETOSPID
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/ 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)