RE: Determining the program name from an after logon trigger
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.
Database Administrator II
Commonwealth of Pennsylvania
Department of Health
Bureau of Information Technology
2150 Herr Street
Harrisburg, PA 17103
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
Subject: Re: Determining the program name from an after logon trigger
WHERE sid = [currentsid]
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
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;
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 220.127.116.11 running on Solaris/SunOS 5.9).
(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