RE: Determining the program name from an after logon trigger

From: Freeman, Donald <dofreeman_at_state.pa.us>
Date: Thu, 26 Mar 2009 15:52:04 -0400
Message-ID: <55264C4C0484A547B34C0B1A28E219EA321D164FF7_at_ENHBGMBX01.PA.LCL>

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_at_state.pa.us<mailto:dofreeman_at_state.pa.us>

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Toon Koppelaars Sent: Thursday, March 26, 2009 3:46 PM
To: peter.schauss_at_ngc.com
Cc: oracle-l_at_freelists.org
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_at_ngc.com<mailto:peter.schauss_at_ngc.com>> 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
--

http://www.freelists.org/webpage/oracle-l

--

Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com<http://www.RuleGen.com>
thehelsinkideclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com>

(co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13<http://www.RuleGen.com/pls/apex/f?p=14265:13>

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 26 2009 - 14:52:04 CDT