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.
--
-- Add/Remove the traces you require
 
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''';
--   execute immediate 'alter session set events ''10046 level 1''; -- 11g onwatds simplier syntax is available. 
--   execute immediate 'alter session set events ''8103 trace name errorstack level 3''';
--   execute immediate 'alter session set events ''10236 trace name context forever, level 1''';
--   execute immediate 'alter session set max_dump_file_size=''UNLIMITED''';
--   execute immediate 'alter session set db_file_multiblock_read_count=1';
--   execute immediate 'alter session set tracefile_identifier=''ORA8103''';
  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