Determining the program name from an after logon trigger

From: Schauss, R. Peter (IT Solutions) <"Schauss,>
Date: Thu, 26 Mar 2009 14:05:12 -0500
Message-ID: <>

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
	 if user = '<username>' then
	 	execute immediate 'alter session set
		execute immediate 'alter session set
		execute immediate 'alter session set events ''10046
trace name context forever, level 8''';
	 end if;


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 running on Solaris/SunOS 5.9).

Peter Schauss
-- Received on Thu Mar 26 2009 - 14:05:12 CDT

Original text of this message