Enable SQL Trace in Oracle Apps R12

 

 Enable SQL Trace in Oracle Apps R12

Introduction

SQL trace can be enabled in any E-Business module via different ways. The following table shows all possible ways for enabling trace in a form, self-service page, concurrent program, specific application user, instance wide, etc.

Applications Form:

1) Set the value for profile option Utilities: Diagnostics to ‘Yes’ at the user-level
2) Navigate to form where you want to trace
3) Turn on Tracing by using the menu option: Home > Diagnostics > Trace > Trace with waits
4) A pop-up with the trace file name and location gets displayed. Note down the trace filename
5) Proceed with steps that need to be traced. Once done tracing, exit the Application
6) Retrieve the raw trace file using the filename (from Step 4) located on the db server

Self Service Page:

1) Set the value for the profile option FND: DIAGNOSTICS to ‘Yes’ at user-level.
2) Navigate to the Self-Service page where you want to trace
3) Click the Diagnostics icon at the top-right of the page
4) Select Set Trace Level radio button and click ‘Go’
5) Select Trace with  waits (recommended) and click ‘Save’
6) Select ‘Home’ and proceed with performing your screen processing
7) Disable trace once you are done: click on Diagnostics > Set Trace Level > Disable Trace
8) Write down the ‘Trace Ids’ provided on the left side of the screen
9) Logout/Exit from the application
10) Retrieve raw trace file using the Trace Ids (from step 8) and/or the tracefile_identifier (set by default to the userid)

Concurrent Program Definition:

1) Choose an appropriate responsibility and select the Concurrent > Program > Define screen
2) Search for the concurrent program you want to trace
3) Check the Enable Trace box to turn on tracing for the concurrent program
4) Submit and run the concurrent program
5) Write down the request_id of your concurrent program job
6) Go back to the Define screen and un-check the Enable Trace box for this concurrent program
7) Retrieve the raw trace file using the request_id (from step 5) and/or the tracefile_identifier (set by default to the userid)

Concurrent Program Submission:

1) Set the value for the profile option Concurrent: Allow Debugging to ‘Yes’ at user-level
2) Choose the appropriate responsibility and concurrent program to be executed
3) Click on the Debug Options button
4) Enable tracing by selecting the SQL Trace Check box and choose the desired trace level
5) Confirm your selection by clicking the OK button
6) Submit the concurrent program
7) Write down the request_id of your concurrent program job.
8) Retrieve the raw trace file using the request_id (from step 7) and/or the tracefile_identifier (set by default to the userid)

Profile Option:

1) If you are activating trace for your own account, navigate to Profile > Personal
2) Press F11, type Initialization% in the Profile Name column, then hit CTRL-F11
3) If you are enabling trace for another user, navigate to Profile > System
4) Check User and Type in the Username to be traced
5) Type Initialization% in the Profile box and Hit ‘Find’
6) In the User box for Initialization SQL Statement – Custom, type the following statement [Quotes in the statement are all ‘Single’ quotes]:

BEGIN FND_CTL.FND_SESS_CTL(”,”,’TRUE’,’TRUE’,”,’ALTER SESSION SET TRACEFILE_IDENTIFIER=”User_Trace” MAX_DUMP_FILE_SIZE=unlimited EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL 8”’);END;

7) Save. Logout then Login back to applications as the user for whom you turned on tracing, and promptly recreate the problem.
8) Go back to the Profile option in the Form application and delete the Initialization SQL statement, and Hit ‘Save’, exit the Application
9) Identify and retrieve the trace file using the tracefile_identifier specified in Step 6

Session Level:

You can enable trace on session level using the following commands:

— For current session only
SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 8’;
SQL> ALTER SESSION SET EVENTS ‘10046 trace name context off’;

— For current session / other session
SQL> CONN sys/password AS SYSDBA;   — user must have SYSDBA
SQL> ORADEBUG SETMYPID;                  — debug current session
SQL> ORADEBUG SETOSPID 1234;         — debug session with OS Process ID (SID)
SQL> ORADEBUG SETORAPID 123456;  — debug session with Oracle Process ID (SPID)
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8;
SQL> ORADEBUG TRACEFILE_NAME;    — display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

System Level:

You can enable trace on the entire system (Instance wide) using the following commands:

SQL> alter system set events ‘10046 trace name context forever,level 8’;

OR set the following event in init.ora file:

event=”10046 trace name context forever,level 8″