Saturday, August 1, 2009

How to: enable oracle sql trace part 2 - other session

Method 1: using dbms_support package


SQL> @?/rdbms/admin/dbmssupp.sql
Package created.
Package body created.

SQL> begin
2 dbms_support.start_trace_in_session(
3 sid=>159,
4 serial=>16,
5 waits=>true,
6 binds=>true);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_support.stop_trace_in_session(
3 sid=>159,
4 serial=>16);
5 end;
6 /
PL/SQL procedure successfully completed.


Method 2: using dbms_system.set_ev to set event 10046
(Important: nm is '', instead of null).


SQL> begin
2 dbms_system.set_ev(
3 si=>159,
4 se=>16,
5 ev=>10046,
6 le=>12,
7 nm=>'');
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> begin
2 dbms_system.set_ev(
3 si=>159,
4 se=>16,
5 ev=>10046,
6 le=>0,
7 nm=>'');
8 end;
9 /
PL/SQL procedure successfully completed.


Method 3: using dbms_system package


SQL> begin
2 dbms_system.set_sql_trace_in_session(
3 sid=>159,
4 serial#=>16,
5 sql_trace=>true);
6 end;
7 /
PL/SQL procedure successfully completed.


SQL> begin
2 dbms_system.set_sql_trace_in_session(
3 sid=>159,
4 serial#=>16,
5 sql_trace=>false);
6 end;
7 /
PL/SQL procedure successfully completed.


Method 4: using dbms_monitor package (10g onwards)


SQL> begin
2 dbms_monitor.session_trace_enable(
3 session_id=>159,
4 serial_num=>16,
5 waits=>true,
6 binds=>true);
7 end;
8 /
PL/SQL procedure successfully completed.

SQL> begin
2 dbms_monitor.session_trace_disable(
3 session_id=>159,
4 serial_num=>16);
5 end;
6 /
PL/SQL procedure successfully completed.


Method 5: using oradebug to turn on event 10046


SQL> oradebug setospid 9718
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.