This is a COTS package so I do not have access to the program. - Peter Schauss ________________________________ From: Freeman, Donald [mailto:dofreeman@xxxxxxxxxxx] Sent: Thursday, March 26, 2009 3:52 PM To: 'toon.koppelaars@xxxxxxxxxxx'; Schauss, R. Peter (IT Solutions) Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: Determining the program name from an after logon trigger I'm not an expert but if you have access to the application you can use dbms_application_info and add program information to v$session. Not sure if you can do it in 8.1.7. The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION and V$SESSION_LONGOPS views to make tracking of session activities more accurate. Donald Freeman Database Administrator II Commonwealth of Pennsylvania Department of Health Bureau of Information Technology 2150 Herr Street Harrisburg, PA 17103 dofreeman@xxxxxxxxxxx ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Toon Koppelaars Sent: Thursday, March 26, 2009 3:46 PM To: peter.schauss@xxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Determining the program name from an after logon trigger Can SELECT program FROM v$session WHERE sid = [currentsid] Help you? On Thu, Mar 26, 2009 at 8:05 PM, Schauss, R. Peter (IT Solutions) <peter.schauss@xxxxxxx> wrote: 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 -- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com thehelsinkideclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13