Threshold login triggers for Oracle 10046 event trace collection
By glennf on Jul 11, 2006
The problem with most packaged applications, is that they all use the \*same\* userid. For this Oracle 10G environment, I used this fact to filter only connections of the type that I wanted to sample. I wanted to gather 10046 event trace data when the number of connections was 10, 20, or 30. To achieve this, I used a logon trigger and sampled the number of sessions from v$session to come up with the connection count. I have found this little trick to be very useful in automating collection without modifying the application. I hope this can be useful to you as well.
create or replace trigger trace_my_user after logon on database DECLARE mycnt int; BEGIN SELECT count(\*) INTO mycnt FROM v$session WHERE username='GLENNF'; if (user='GLENNF') and ((mycnt=10) or (mycnt=20) or (mycnt=30)) then dbms_monitor.session_trace_enable(null,null,true,true); end if; end; /