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.