Category: oracle tracing

To start tracing:


create or replace trigger user_logon_trg
after logon on database
begin
if USER = ‘xxxx’ then
execute immediate
‘Alter session set events ”10046 trace name context forever, level 8”’;
end if;
end;
/


/* Login a new session as User ‘xxxx’ and execute your selects to be traced */




NOTE: The following syntax can also be used within the if logic to also get the user name or to add more granularity by specifying a host name where the connection originated.


IF SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘<USER_NM>’ AND SYS_CONTEXT(‘USERENV’,’HOST’) = ‘<HOST_NM>’ THEN


To stop tracing: via LogOff Trigger (needs to be created before logging off)


create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = ‘xxxx’ then
execute immediate
‘Alter session set events ”10046 trace name context off”’;
end if;
end;
/