RE: Determining the program name from an after logon trigger
- From: "Schauss, R. Peter (IT Solutions)" <peter.schauss@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Mar 2009 15:02:45 -0500
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
Other related posts:
