Oracle Trace reference

g-se-tr.sql

Script below enables trace in another user session. It will list all three possible ways to achive this, through a) SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, b) SYS.DBMS_SYSTEM.SET_EV and c) ORADEBUG.

col set_trace for a136 heading "ENABLE TRACE IN ANY SESSION"
 
select
 'EXEC sys.dbms_system.set_bool_param_in_session -
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''timed_statistics'', bval => true);' 
 || chr(10) ||
 'EXEC sys.dbms_system.set_int_param_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''max_dump_file_size'', intval  => 2147483647);' 
 || chr(10) || chr(10) ||
 'EXEC sys.dbms_support.start_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ', waits => true, binds => true);' 
 || chr(10) ||
 'EXEC sys.dbms_support.stop_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ');' 
 || chr(10) || chr(10) ||
 'EXEC sys.dbms_system.set_sql_trace_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', sql_trace => TRUE); -- !! No waits and bind data'
 || chr(10) ||
 'EXEC sys.dbms_system.set_ev - 
 ( si => ' || s.sid || ', se => ' || s.serial# || ', ev => 10046, LE => 12, nm => '''');'
 || chr(10) || chr(10) ||
 'ORADEBUG SETORAPID ' || p.pid || ' | ORADEBUG SETOSPID ' || p.spid
 || chr(10) ||
 'ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12' set_trace
  from v$session s, v$process p
 where s.paddr = p.addr(+)
   and s.sid     like '&SID'
   and s.username   like '&USERNAME'
   and nvl( upper( s.osuser), 'null') like '&OSUSER'
/
 
undef sid osuser username

[SYSTEM@DB01.REMIDIAN.COM]
SQL> @c:\g-se-tr
Enter value for sid: %
Enter value for username: REMI
Enter value for osuser: %
ENABLE TRACE IN ANY SESSION
-------------------------------------------------------------------------------------------
EXEC sys.dbms_system.set_bool_param_in_session -
 ( sid => 49, serial# => 100, parnam => 'timed_statistics', bval => true);
EXEC sys.dbms_system.set_int_param_in_session -
 ( sid => 49, serial# => 100, parnam => 'max_dump_file_size', intval  => 2147483647);
 
EXEC sys.dbms_support.start_trace_in_session -
 ( sid => 49, serial => 100, waits => true, binds => true);
EXEC sys.dbms_support.stop_trace_in_session -
 ( sid => 49, serial => 100);
 
EXEC sys.dbms_system.set_sql_trace_in_session -
 ( sid => 49, serial# => 100, sql_trace => TRUE); -- !! No waits and bind data
EXEC sys.dbms_system.set_ev -
 ( si => 49, se => 100, ev => 10046, LE => 12, nm => '');
 
ORADEBUG SETORAPID 18 | ORADEBUG SETOSPID 832
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

10046 Trace levels

• 0 – no statistics
• 1 – basic statistics CURSOR, PARSE, EXEC, FETCH ERROR, SORT UMAP, ERROR, UMAP, STATS and XCTEND.
      - This is the same as setting sql_trace=true.
• 2 – same as level 1, do not know why they have this one.
• 4 – same as level 1 except adds BIND section.
• 8 – same as level 1 except with wait events.
• 12 – combines all levels 4 and 8.

Enabling trace for specific sessions using logon trigger

Logon trigger below enables trace upon logon for a specic user

[SYSTEM@DB01.REMIDIAN.COM]
SQL> create or replace trigger trg_logon_trace
  2    after logon on database
  3  begin
  4    if sys_context( 'USERENV', 'SESSION_USER') = 'REMIVISSER' and
  5       sys_context( 'USERENV', 'OS_USER') = 'REMIVISSER'
  6    then
  7      -- enable level 12 tracing (includes bind data and wait events)
  8      execute immediate
  9        'alter session set events ''10046 trace name context forever, level 8''' ;
 10    end if ;
 11  exception
 12    when others then
 13      null ;
 14  end;
 15  /
 
Trigger created.
 
[SYSTEM@DB01.REMIDIAN.COM]
SQL> 

Make sure owner of trigger has at least the following privileges, create trigger, administer database trigger, alter session. (see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm for reference of sys_context)

Check which event is set in current session

[SYSTEM@DB01.REMIDIAN.COM]
SQL> alter session set events '10046 trace name context forever, level 8';
 
Session altered.
 
[SYSTEM@DB01.REMIDIAN.COM]
SQL> declare
  2    /* Supplied by Ken Robinson of Oracle Technical Support. */
  3    event_level pls_integer;
  4  begin
  5    dbms_output.put_line( 'EVENT# LEVEL');
  6    dbms_output.put_line( '------ -----');
  7    for i in 10000..10999 loop
  8      sys.dbms_system.read_ev( i, event_level);
  9      if ( event_level > 0)
 10      then
 11        dbms_output.put_line( to_char(i, '99999') || ' '|| to_char( event_level, '9999'));
 12      end if;
 13     end loop;
 14  end;
 15  /
EVENT# LEVEL
------ -----
 10046     8
 
PL/SQL procedure successfully completed.