Logon Trigger question

[jlakhani]

I used this trigger in 8i to do some tracing on a unix db and it has wroked but for some reason in 9i it doesnt work.

I wanted to trace a specific user's whose session id I cannot figure out due to to the fact that it the process connects and disconnects often..

any clues?

CREATE OR REPLACE TRIGGER LOG_TRIGGER AFTER
LOGON ON DATABASE begin
if user = 'OPS$KFOO' then
execute immediate 'alter session set sql_Trace=true';
end if;
end;

[marist89]

Any errors? (check your alert.log)

[jlakhani]

marist, i didnt see any error messages...do you have an alternative method of tracing user sessions?

[davey23uk]

this works for me

create or replace trigger login_trigger after logon on database
declare
x varchar2(20);
y number;
z number;
begin
select sys_context('USERENV','SESSION_USER') into x from dual;
if x = 'XXX' THEN
select sid into y from v$mystat where rownum < 2;
select serial# into z from v$session where sid = y;
dbms_system.set_ev(y,z,10046,4,'');
end if;
end;
/

[jlakhani]

Thanks davy...