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