Trace sessions in Oracle

Your primary All Database information source.

23 November 2009 561 views No Comment

1. Enable tracing and set the maxiumum dump file size to unlimited:

SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> ALTER SYSTEM SET max_dump_file_size = unlimited;

2. find sid and serial for session you want to trace:

SQL> SELECT sid, serial#, username from v$session;

3. start tracing:

SQL> execute dbms_system.set_sql_trace_in_session(sid, serial, true);

Now, all query and operation from the session are traced.

4. stop tracing:

SQL> execute dbms_system.set_sql_trace_in_session(sid, serial, false);

Read the trace file:

You can find trace files in user dump file destination (UDUMP):

SQL> SHOW parameter user_dump_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------
user_dump_dest                       string      D:\ORACLE\PRODUCT\ADMIN\ANDREDB\UDUMP

You can format trace files using tkprof command from your shell: