To debug a form we can use trace files.
1. Log in to the environment that will be traced.
2. Set the profile option “Utilities:Diagnostics“ (internal name DIAGNOSTICS) user-level value to “Yes”.
3. Choose the relevant responsibility for the form and navigate to the form that will be traced.
4. Enable SQL Trace using the menu option : Help > Diagnostics > Trace > Trace with waits. Also select “Unlimited Trace File Size” to ensure an unlimited size for the trace file.
5. The trace file name and location are displayed in a dialog box. Make a note of the trace file name. This trace will be on the database tier.
6. Execute the required actions on the Form.
7. When complete, exit the form and log out.
8. Disable SQL Trace using the menu option : Help > Diagnostics > Trace > No Trace
9. Obtain the raw trace file from the trace file location, using the filename that was displayed above when enabling the trace. Alternatively the trace file identifier (user name), process id or timestamp can be used to identify the trace file.
The initialization parameter user_dump_dest defines the directory to which the trace file is written. Except for 11g, where initialization parameter<diagnostic_dest> (if set) will define this as follows:
10. Use the tkprof command to produce a formatted file.
The following typical command is recommended for generating TKPROF files:
tkprof <filename1> <filename2> sort= fchela,exeela,prsela
Where <filename1> is the input trace file or a consolidated file of traces produced by the trcsess utility and <filename2> is the file to which TKPROF writes its formatted output.