RE: Determining the program name from an after logon trigger

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Thu, 26 Mar 2009 15:55:12 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA4AC_at_EXCNYSM0A1AJ.nysemail.nyenet>

Peter,

You can add the following to your login trigger to get the program name:

  CURSOR c1 IS
    SELECT UPPER(program) program, username,

           osuser, terminal,
           sys_context('USERENV','IP_ADDRESS') ip_addr
      FROM V$SESSION
      WHERE AUDSID = USERENV('SESSIONID');

c1_rec c1%ROWTYPE;

BEGIN    OPEN c1;
   FETCH c1 INTO c1_rec;
   CLOSE c1;
END; Tom

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, R. Peter (IT Solutions)
Sent: Thursday, March 26, 2009 3:05 PM
To: oracle-l_at_freelists.org
Subject: Determining the program name from an after logon trigger

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

--

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