Session Trace

How to enable trace at database level?
Ans : set init.ora parameter sql_trace

How to enable trace for a session?
Ans: Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.

How to enable trace for other session?
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
Eg: To enable trace for sql session with sid 8
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);
PL/SQL procedure successfully completed.

To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);

What is the location of inint.ora ?
Ans : $RDBMS_ORACLE_HOME/dbs

What is that trace files contains and the utiliy used to read them?
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

How to find trace file for a given concurrent request id?
Ans : Go to $RDBMS_ORACLE_HOME/admin//udump
Grep “ “ *

How does one enable trace in the Oracle Application screens / forms?
One can enable trace through the forms by using the Help menu, choosing the daignostics menu,
trace and then selecting the appropriate trace for your needs. Most commonly if debugging an error,
you should at least provide trace with binds.
When debugging a performance issue,
you may consider using trace with binds and waits.

For example, the following is the navigation to enable trace in a form:
Goto the Oracle Applications
Login
Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.
Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.

How does one enable trace for a concurrent program?
A simple way to enable trace in a concurrent program is
to review the concurrent program definition and select trace enabled.
This will enable trace without binds for all users that run the program.

For example, the following steps could be used.
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace