Determining the program name from an after logon trigger

  • From: "Schauss, R. Peter (IT Solutions)" <peter.schauss@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 26 Mar 2009 14:05:12 -0500

I need to force a 10046 trace for a program which runs scheduled reports
against our data warehouse database.  The only way that I know how to do
this is to create an after logon trigger on the username under which
this program connects.  I have done this successfully before to look at
performance bottlenecks for the ETL loads.  The code for the trigger is:

------------------------------------------------------------
CREATE OR REPLACE TRIGGER <username>.trace_user after logon on
siebel.schema
begin
         if user = '<username>' then
                execute immediate 'alter session set
timed_statistics=true';
                execute immediate 'alter session set
max_dump_file_size=unlimited';
                execute immediate 'alter session set events ''10046
trace name context forever, level 8''';
         end if;
end;

--------------------------------------------------------------------

My problem in this case is that the report program uses the same
username as the ETL and I do not want to create and I do not want to
create traces for those processes.  The sys_context('USERENV',...)
function can return a good bit of information about the current process
but, as far as I can tell, not the name of the calling program.  Is
there any easy way to do this?  (This is Oracle 8.1.7.4 running on
Solaris/SunOS 5.9).

Thanks,
Peter Schauss
--
//www.freelists.org/webpage/oracle-l


Other related posts: