Wednesday, July 20, 2011

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

No comments: