RE: Determining the program name from an after logon trigger
- From: "Freeman, Donald" <dofreeman@xxxxxxxxxxx>
- To: "'toon.koppelaars@xxxxxxxxxxx'" <toon.koppelaars@xxxxxxxxxxx>, "peter.schauss@xxxxxxx" <peter.schauss@xxxxxxx>
- Date: Thu, 26 Mar 2009 15:52:04 -0400
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<mailto: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<mailto: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<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>
Other related posts:
