How to trace Web Session in Oracle Applications

This process can be used to trace any action, anywhere in Oracle Applications and be very useful for the Self-Service Web Applications as there is no utility defined like in the Forms.

a. First make sure the necessary profile has the proper permissions.
1. Log onto the Applications Forms with the Application Developer Responsibility
2. Navigate to the Profile menu
3. Query up the profile name “FND_INIT_SQL”
4. In the bottom block of the form, make sure that ALL checkboxes are checked Typically, you will have to enable the checkboxes under “User Access” to make it “Visible” and “Updatable”.

b. Now switch to the System Administrator Responsibility
1. Navigate to – Profile – System
2. On the “Find System Profile Values” form, make sure the checkboxes for “User” and “Profiles with no Values” are checked
3. Beside the “User” checkbox, use the LOV to select the user who’s activity you need to trace
4. In the “Profile” field, enter the following profile and click the button: ‘Initialization SQL Statement – Custom’
5. In the “System Profile Values” form, enter the following in the User Field: (This is one line and all single quotes)

begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL 12” tracefile_identifier=”spurohit” max_dump_file_size=”unlimited”’); end;


7. In another browser window, login as the user you are going to trace and prepare to reproduce the problem
8. Once you are ready to reproduce the problem, go back to the Applications Forms and Save the profile change
9. Reproduce the problem
10. Back in the Applications form, set profile to null so it does not trace anymore and Save the change
11. The trace will be located in the user_dump_dest. To find location run the following in SQL*Plus: select value from v$parameter where name = ‘user_dump_dest’;
12. The trace file will have current date/time and can be identified with the word spurohit in it.

— To know all steps in details and with picture, do refer below url

thanks – Shiv

Trace a Concurrent Request And Generate TKPROF File

Hello Friends, As in Oracle Application, there always need to tune programs , procedure and it is always difficult to make a perfect program, here i am trying to put a “How to” on tkprof and surely like to know if you find it useful.

How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program 

  • Responsibility: System Administrator
  • Navigate: Concurrent > Program > Define
  • Query Concurrent Program
  • Select the Enable Trace Checkbox 

Turn On Tracing

  • Responsibility: System Administrator
  • Navigate: Profiles > System
  • Query Profile Option Concurrent: Allow Debugging
  • Set profile to Yes

 Run Concurrent Program With Tracing Turned On

  • Logon to the Responsibility that runs the Concurrent Program 
  •  In the Submit Request Screen click on Debug Options (B)
  • Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT ‘Request id: ‘||request_id ,  ‘Trace id: ‘||oracle_Process_id,  ‘Trace Flag: ‘||req.enable_trace,  ‘Trace Name:  ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,  ‘Prog. Name: ‘||prog.user_concurrent_program_name,  ‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,  ‘Status : ‘||decode(phase_code,’R’,’Running’)  ||’-‘||decode(status_code,’R’,’Normal’),  ‘SID Serial: ‘||ses.sid||’,’|| ses.serial#,  ‘Module : ‘||ses.module  from fnd_concurrent_requests req, v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  fnd_executables execname  where req.request_id = &request  and req.oracle_process_id=proc.spid(+)  and proc.addr = ses.paddr(+)  and’user_dump_dest’  and’db_name’  and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id  and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id; 





3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql                      statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS 

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10” long running queries



$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)’ print=10 





Thanks – Shivmohan Purohit