Hi,
I have created a trigger on DB user to trace the SQL session used by an application for a particular DB user.
CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN
AFTER LOGON ON DATABASE
WHEN ( USER = 'NETSERVICOS2CM' )
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "all_net2cm"';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
This trigger is generating the .TRC files about 1 month ago but now when i tried to use the same trigger it not creating the trace files in file path
/data/app/oracle11g/diag/rdbms/sid11g/sid11g/trace
I have tried creating a new trigger like this to check if the trigger is firing or not --
create table trace_trigger (user_nm varchar2(50), action varchar2(2000));
CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN
AFTER LOGON ON DATABASE
WHEN ( USER = 'NETSERVICOS2CM' )
declare
l_act varchar2(2000);
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "net2cm"';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
insert into trace_trigger values ('net2cm', 'fire');
EXCEPTION
WHEN OTHERS THEN
l_act := sqlerrm;
insert into trace_trigger values ('net2cm', l_act);
END;
I create this table into DBA user and the user which i want trace so after compiling the trigger when i execute some SQL query on the user in my case 'NETSERVICOS2CM' it does not get invoked because i check the trace_trigger table and it shows no rows selected which i think because of trigger not getting fire.
i am not understanding the behavior as it was working perfectly some days before... now its not working.
Any suggestion please what is that which is preventing this trigger to fire !
I am using --
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production