A Logon Trigger to Trace a User Session

Description

The following DDL script can be used to create a logon trigger which when enabled will SQL trace any user that logs on witht ehdefined user name.

The script takes a parameter of USER_ID which is the user id to be traced. By default the trigger will trace at level 12.

cr_user_trace_trg.sql

--
-- Trigger Name: USER_TRACE_TRG
--
-- Description: Used to SQL trace a user session.
--
 
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = '&USER_ID'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License