Enabling Trace in the Database

Instructions for running a SQL Trace for a single session

1. Verify DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure is available. If not, DBA must install it.
2. Clean out any old *.trc files in the udump directory so it is easier to find the generated .trc file. To determine the udump directory run this SQL connected as system:
Select value from v$parameter where name = ‘user_dump_dest’;
3. Have the user performing the test logon to database and proceed to the point just prior to executing the function that needs tracing. Make sure there is only one occurrence of this user connected to Oracle.
4. Have the DBA set timed statistics on. Use this SQL, connect as system:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
5. Run this SQL, connected as system, to determine the sid and serial#. Replace USERNAME with correct username.
select sid', 'serial#', 'username', 'machine
from v$session where username = ‘USERNAME’;
6. Have the DBA set SQL Trace on for the user session. Use this SQL, using the sid and serial# from the previous step:
Execute dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
7. Have the user perform the desired function.
8. Have the user logoff EMPAC. This will inactivate SQL Trace.
9. Locate the new file in the udump directory.
10. Have the DBA run tkprof against the new file. This is the format:
Tkprof filename.trc filename.lst explain=username/password@instance sys=no
11. Support will need both the filename.trc file and the filename.lst file.
12. Turn timed statistics off. Use this SQL:
ALTER SYSTEM SET TIMED_STATISTICS = FALSE;