Viswamitra's blog

Below is the script to create a logon trigger to trace sessions of a particular user.

CREATE OR REPLACE TRIGGER USER_TRACE_TRG AFTER LOGON ON DATABASE
declare
SI int;
SE int;
FILE_NAME varchar2(200);
begin
IF USER = ‘CS16ORACLETS’ —- Give the user name here
THEN
select sid,serial# into SI,SE from v$session where audsid = userenv(‘SESSIONID’);
FILE_NAME:=’CSPERF_’||SI||’_’||SE;
execute immediate ‘ALTER SESSION SET tracefile_identifier = ‘||FILE_NAME;
execute immediate ‘alter session set sql_trace = true’;
execute immediate ‘ALTER SESSION SET EVENTS ”10046 trace name context forever, level 12”’;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
/

This will trace only the sessions created after the execution of this script. If the session is already exist kill the session and create again to trace the session.