Oracle DB: Automatic tracing for all sessions of a user

Hi,

SQL tracing is nice to find out details about several issues. But there is no out-of-the-box solution to enable tracing for a user, only for session- or system-wide tracing. But there’s a trick to close this gap. It’s old, but stil useful: Use a logon trigger to enable tracing for all sessions of a user (or for all users except x, y and z). And use a logoff trigger to disable tracing again.

Here’s some code how one might want to do a logon trigger for all users except system users and within a specific time window:

CREATE OR REPLACE TRIGGER trace_ddi_logon
after logon on database
begin
if (    sys_context('USERENV', 'SESSION_USER') not in ('SYS', 'SYSTEM')
  and to_char(sysdate, 'HH24:MI') >= '06:30'
  and to_char(sysdate, 'HH24:MI') <= '20:00'
  )
then
  execute immediate 'alter session set timed_statistics=true';
  execute immediate 'alter session set max_dump_file_size=10000';
  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end if;
end;
/
SHOW ERRORS;

Same for the logoff trigger:

CREATE OR REPLACE TRIGGER trace_ddi_logoff before logoff on database
begin
if (
  sys_context('USERENV', 'SESSION_USER') not in ('SYS', 'SYSTEM')
   )
then
  execute immediate 'alter session set events ''10046 trace name context off''';
  end if;
end;
/
SHOW ERRORS;

Thanks to Dietmar for the hint! Hope this helps in some situation,

Usn




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Oracle DB: Automatic tracing for all sessions of a user”

  1. Usn’s IT Blog » Oracle: Tracing of another session Says:

    [...] simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session [...]

Leave a Reply