Create a User Event Trace in 11i / R12

Print this post

A user event trace is very handy for tracing sql operations to debug various issues. The benefit of a user event trace is that it is linked to a specific user so that only code run by this user is traced. This makes diagnosis easier when compared to similar tracing methods at the database level where all user calls are traced. Step 1 Login to Oracle Applications and select the System Administrator responsibility. Choose Profile - System Step 2 In the find profile field select the user which you wish to trace. In the profile field enter 'Initialization SQL Statement - Custom" Select find Step 3 In the find profile results form copy and paste the following into the 'user' field. Do not update the site level field. begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');end; Note: copy and paste the above as one line. Failure to paste the values properly or pasting the values with incorrect syntax will result in this user not being able to login   Step 4 Save the profile option change. Stop and start the java virtual machine for the change to take effect 11i: use adapcctl.sh r12: use adoacorectl.sh Step 5 Login to the application and reproduce the issue. Then quickly log off. Try and avoid any un-necessary keystrokes as this simply makes the log files larger and the issue hard to pinpoint in the logs. Step 6 login to unix/windows as the oracle user. Navigate to the user dump destination which us usually $ORACLE_HOME/admin/<context>/udump You will see a series of trace files generated during the time of your issue reproduction. Tkprof all of the generated files and upload both the raw and tkprof files to your service request You can also check the user dump destination via the following: sql> show parameter user_dump_dest;