Re: Logon Trigger for SQL TRACING using (Third party tool)

"BSena" <bill_sena_at_hotmail.com> wrote in message news:2ceca914.0403112127.7dbaf612_at_posting.google.com...
> Thanks Pete,
>
>
> As you thought, I want to trace the use of a third party
> Tool.
> The users of this tool logon the same single database with different
> service names.
> I have visited your papers about SQL TRACING.
> It contains lots of information about SQL TRACE.
> But at that stage I am little bit confused.
> If possible, Could you give some sample with respect to my query (SQL
> tracing for third party tool via logon trigger),in order to the trace
> that specific
> session which is rinnung this third party tool.
>
> Thanks
>
> Bill

You might find the following helpful. There are a couple of oddities. I massage the username because I am using a domain account for testing purposes. You probably wouldn't need to do this on *nix. I have cut out some info that might identify the app or the os environment we have so the trigger might not compile without a bit of editing.

CREATE OR REPLACE TRIGGER TRG_<SCHEMA_NAME>_LOGON

AFTER LOGON ON <SCHEMA_NAME>.SCHEMA

DECLARE v_username varchar2(255);

begin

/* get the osuser */

select sys_context('userenv','os_user') into v_username from dual;

v_username := substr(v_username,instr(v_username,'\')+1);

if v_username = '<oslogon_name>' then

execute immediate 'alter session set events ''10046 trace name context forever,level 8''';

end if;

 end;

/

cheers

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK