Fun with Logon Triggers

Sometimes a process happens too fast to be traced effectively, but it's happening slowly enough to make your users angry.

Here's an example of a logon trigger setup to make a trace happen, we're using it to diagnose an issue with slow response from an ODBC connection, using user account ADMIN_JOSH:

DROP TRIGGER SYSTEM.JOSH_LOGINTRIGGER;
 
CREATE OR REPLACE TRIGGER SYSTEM.Josh_LoginTrigger AFTER LOGON ON ADMIN_JOSH.SCHEMA
BEGIN
   execute immediate 'ALTER SESSION SET tracefile_identifier = MYTRACEFILE';
   execute immediate 'alter session set sql_trace=true';
END;
/

Note if you get the following error, the owner of the trigger needs the alter session priv (in this case system).

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 2