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