Tuesday, December 3, 2013

Trace user sessions with login trigger

In order to trace sessions created by a specific user, you can use following login trigger:

CREATE OR REPLACE TRIGGER tgardba.TRG_SESSION_TRACE
AFTER LOGON
ON DATABASE
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
BEGIN
  IF (USER = 'USERNAME') THEN
    execute immediate sqlstr;
  END IF;
END TRG_SESSION_TRAC;
/