Enable Trace for Specific User

Oracle has provided an option to execute custom code for every session opened in the database through a system profile. This profile called “Initialization SQL Statement - Custom” (the short name is 'FND_INIT_SQL') and allows customize sql/pl*sql code.

Once setting this profile in user level, each session opened for this user will first execute the code within the profile. No matter which type of activity the user does – Forms, CRM, Concurrent request, or anything else that opens a database session – the content of this profile will be executed.

So, clearly we can use this capability to enable Trace for users sessions.

Steps to enable trace for specific user:

1. Login with “Application Developer” responsibility
2. Open the “Create Profile” form –> Query the profile “FND_INIT_SQL”
3. Make sure that “visible” and “updateable” are checked in user level.
   
    

1.    Log onto System Administrator Responsibility

2.    Navigate: Profile > System

3.    Search for the profile option Initialization SQL Statement - Custom at     USER level.
Make sure you enter the Username for the user you want to turn on trace for.

4.    Enter the following sql statement into the User Level profile option.
This must be one complete line of text. Ensure you change USERID to the actual username of the user.

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'USERID' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END; 




  

Ensure that the user has logged out and has no session open.

SELECT user_name, count(*) How_many_sessions
FROM icx_sessions icx, fnd_user u
WHERE icx.user_id = u.user_id
AND last_connect > sysdate - (4/24)
AND disabled_flag != 'Y'
AND PSEUDO_FLAG = 'N'
GROUP BY user_name
ORDER BY 2 desc




1. Now, after the user logout from the application (the user you enabled trace for), the user can login and reproduce the issue.
    
2. When finish to reproduce the issue, you should disable the trace by clearing the profile option value and update it to NULL. (profile “Initialization SQL Statement – Custom” of course..) 
3. The trace file/s will wait for you in your udump (user_dump_dest init’ parameter) directory.
   
   

Since I enabled and disabled the trace quite a few times while investigating my performance issue, I wrote these handy simple programs which enable and disable the trace for a user in a quick and easy manner.

Execute this program to enable trace for a specific user: (substitute step 6 above)

DECLARE   l_ret     boolean;   l_user_id number; BEGIN   select user_id     into l_user_id     from fnd_user    where user_name = '&&USER_NAME';   l_ret := fnd_profile.SAVE(X_NAME        => 'FND_INIT_SQL',                             X_VALUE       => 'BEGIN FND_CTL.FND_SESS_CTL('''','''','''', ''TRUE'','''',''ALTER SESSION SET TRACEFILE_IDENTIFIER=''||''''''''||''&&USER_NAME'' ||''''''''||'' EVENTS =''||''''''''||'' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ''||''''''''); END;',                             X_LEVEL_NAME  => 'USER',                             X_LEVEL_VALUE => l_user_id);   commit;   dbms_output.put_line('Profile has updated successfully'); EXCEPTION   when others then     dbms_output.put_line('Failed to update the profile: '||sqlerrm); END;

Execute this program to disable trace for a specific user: (substitute step 8 above)

DECLARE   l_ret     boolean;   l_user_id number; BEGIN   select user_id     into l_user_id     from fnd_user    where user_name = '&USER_NAME';   l_ret := fnd_profile.DELETE(X_NAME        => 'FND_INIT_SQL',                               X_LEVEL_NAME  => 'USER',                               X_LEVEL_VALUE => l_user_id);   commit;   dbms_output.put_line('Profile has erased successfully'); EXCEPTION   when others then     dbms_output.put_line('Failed to erase the profile: '||sqlerrm); END;