A sample logon trigger to set trace

A sample logon trigger to set trace

Quite often you would like trace to be set for a session as soon as the user logs on. Also you may want to be able to set trace for a specific set of users when they log in. This can easily be done with a database logon trigger. Here is a sample trigger.

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create or replace trigger set_trace after logon on database
2 begin
3 if user not in (’SYS’,'SYSTEM’) then
4 execute immediate ‘alter session set timed_statistics=true’;
5 execute immediate ‘alter session set max_dump_file_size=unlimited’;
6 execute immediate ‘alter session set sql_trace=true’;
7 end if;
8 exception
9 when others then
10 null;
11 end;
12 /

Trigger created.

SQL> sho errors
No errors.
SQL>

OK, that was easy. You can also use the alter session set events ‘10046 trace name context forever,level 12′ syntax if you prefer You can also enable other checks within the trigger if need by using any valid PL/SQL logic that you prefer. One tip is that if you have any troubles with your system trigger and it causes logins to fail is to always include, as I have, an exception handler that calls null; for any error condition. If all else fails you can disable system triggers by setting the parameter _system_trig_enabled=false in the initialisation file. This undocumented / hidden parameter stops the processing of system triggers such as logon triggers.