After Log On Trigger Does Not Fire

[learning_bee]

below is my trigger:

create or replace trigger trace
after logon on database
begin
if user = 'BATCH'
then
execute immediate 'alter session set sql_trace=true';
end if;
end;
/



BATCH user had DBA privilege. and for some reason, I kept getting the error :

*** SESSION ID:(12.26) 2004-12-17 09:53:45.637
Skipped error 604 during the execution of SYSTEM.TRACE
*** 2004-12-17 09:53:45.827
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 10

[DaPi]

I suspect this is like procedures, where you need to grant alter session explicitly to the user - granting via a role is not enough.

[learning_bee]

thanks Dapi.


also what are you tracing when you set level=12???

[waitecj]

It means you get information about binds and about wait events as well as your usual SQL stats.

[DaPi]

http://metalink.oracle.com/metalink/...T&p_id=21154.1