Enable trace with bind and wait for EBS user session, concurrent request.
Set this profile option from user level for which you want to sql trace
Profile Option Name : Initialization SQL Statement - Custom
Level : User
Profile Option Value:
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'TEST_RUN' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;
You can change the level value for below options.
Level Value
0 - No trace.
2 - Normal Sql Trace
4 - Trace with bind
8 - Trace with wait
12 - Trace with bind and wait
SQL to find trace file name for running concurrent request ( if trace enabled )
SELECT DISTINCT user_concurrent_queue_name "Manager",
phase_code,
request_id,
s.inst_id,
s.sid,
s.serial#,
s.status,
s.sql_id,
event,
r.os_process_id,
NVL (s.process, r.os_process_id) PROCESS,
NVL (machine, logfile_node_name) MACHINE,
ROUND (last_call_et / 60, 2) LAST_CALL_ET,
gp.pid Process_id,
gp.spid Server_PID,
gp.tracefile TRACEFILE
FROM gv$session s,
gv$process gp,
fnd_concurrent_requests r,
fnd_concurrent_processes fcp,
fnd_concurrent_queues_tl fcq
WHERE s.process(+) = r.os_process_id
AND s.PADDR = gp.addr
AND s.inst_id = gp.inst_id
AND request_id = &req_id
AND r.controlling_manager = fcp.concurrent_process_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND phase_code = 'R';
