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; /
