Generating Event level Traces in Oracle Applications

Published on by Advait

We have seen how the event traces can be generated for a database user session using Oracle Trace Utility. Now what if we need to generate the trace for some application user (either Apps 11i or E-business suite R12). In this case when a user connects to an application its very difficult to track the session.

For this reason we have a profile option, which can be set to generate trace file for any event we want within the application.

Follow the below steps for enabling the event level tracing within an application.

1) Login to application and go to “System Administrator” responsibility.

2) Navigate to “Profile -> System”

3) In the user field, enter the name of user you want to enable tracing for. This will be your application user.

4) On the profile search screen search for “Initialization SQL Statement – Custom” profile.

5) When the profile is shown you can set the value as

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

All the quotes are single quote here. You can just copy and paste the profile value.

6) DO NOT SAVE THE PROFILE

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. The trace can be identified using the trace identifier we have set – “AppsTrace_10046”. If you have set some different identifier, then you can search using that key word.

Hope this helps !!

2 thoughts on “Generating Event level Traces in Oracle Applications”

  1. 10046 Level 12 also includes the binds which if you do not need may cause your trace file to be so huge. Level 8 includes waits which is usually a very useful information during tuning.

    Time to time using a database logon trigger to trace a specific user actions may also be useful;

    — After Logon Trigger for Tracing
    CREATE OR REPLACE TRIGGER trace_trig
    AFTER LOGON
    ON DATABASE
    DECLARE
    sqlstr VARCHAR2(200) := ‘ALTER SESSION SET EVENTS ”10046 TRACE NAME CONTEXT FOREVER, LEVEL 8”’;
    BEGIN
    IF (USER = ‘YOUR_APP_USER’) THEN
    execute immediate sqlstr;
    END IF;
    END trace_trig;
    /

    Best regards.

    Reply

  2. Yes, thats correct. In case of database session we can limit the size using below command.

    alter session set max_dump_file_size=1M;

    But in case of application, we cannot.

    Thanks for your comments.

    Reply

Leave a comment