RE: Determining the program name from an after logon trigger

From: Scott Canaan <srcdco_at_rit.edu>
Date: Thu, 26 Mar 2009 15:52:49 -0400
Message-ID: <D0A642D37DE30842AA667A9AFFE3951A05F482FE_at_svits11.main.ad.rit.edu>

Select program

From v$session

Where audsid = userenv('sessionid');  

I do a similar select in my logon trigger, but pick up the osuser and machine.  

Scott Canaan '88 (Scott.Canaan_at_rit.edu)

(585) 475-7886

"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.  

-----Original Message-----
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> 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
thehelsinkideclaration.blogspot.com



(co)Author: "Applied Mathematics for Database Professionals"

www.RuleGen.com/pls/apex/f?p=14265:13


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

Received on Thu Mar 26 2009 - 14:52:49 CDT