Detecting program name on logon.

Detecting program name on logon.

Post by vdol » Wed, 24 Oct 2001 22:47:56



I have a program that constantly connects to the database and hangs
resources
for the database. It is using JDBC client, so all information in
V$SESSION is non-specific (there is no terminal, os_user or any other
fields). It is also using generic user name that other programs use.
All I have to go by is the external program name (it says ORACLE.EXE
(P001)). I wanted to come up with the after logon trigger that would
disconnect session with such a program name.
However, in the userenv there is no program name attribure, there is
also no sessionid so that I can reference V$SESSION.
ANy ideas on how I can do that?
Thanks.
 
 
 

Detecting program name on logon.

Post by Kevin Bran » Thu, 25 Oct 2001 03:12:46


Assuming Oracle 8i

I would go after the IP_ADDRESS of the offending machine and find out
who/what is causing the problem.  You should be able to get this either
using SYS_CONTEXT or setting "log_status=on" at the tns listener level.
Watch  your listener log file for entries where PROGRAM=<the suspected
program>, get the IP address and track them down.

However, if you use either:

userenv('SESSIONID')
or
sys_context('userenv','sessionid')

the resulting integer can be matched to a session with v$session.audsid.

-Kevin


Quote:> I have a program that constantly connects to the database and hangs
> resources
> for the database. It is using JDBC client, so all information in
> V$SESSION is non-specific (there is no terminal, os_user or any other
> fields). It is also using generic user name that other programs use.
> All I have to go by is the external program name (it says ORACLE.EXE
> (P001)). I wanted to come up with the after logon trigger that would
> disconnect session with such a program name.
> However, in the userenv there is no program name attribure, there is
> also no sessionid so that I can reference V$SESSION.
> ANy ideas on how I can do that?
> Thanks.


 
 
 

Detecting program name on logon.

Post by Lionel Mandrak » Thu, 25 Oct 2001 11:57:52


If you include calls to the Oracle-supplied dbms_application_info
package (ie, the 'set_module' procedure) in your code, you can put
"custom" info into v$session and then easily find other instances of you
application.  Of course, this assumes you control the source code...

HTH


> I have a program that constantly connects to the database and hangs
> resources
> for the database. It is using JDBC client, so all information in
> V$SESSION is non-specific (there is no terminal, os_user or any other
> fields). It is also using generic user name that other programs use.
> All I have to go by is the external program name (it says ORACLE.EXE
> (P001)). I wanted to come up with the after logon trigger that would
> disconnect session with such a program name.
> However, in the userenv there is no program name attribure, there is
> also no sessionid so that I can reference V$SESSION.
> ANy ideas on how I can do that?
> Thanks.

 
 
 

1. How to set Program Name / How to detect a KILL

Hi guys,
I've got two questions I'd appreciate help with...

(1) If / how, using JDBC, can one set the value that appears in sp_who2's
"ProgramName" column?

(2) If / how, using JDBC, can one detect that a stored procedure invocation
in JDBC was terminated using T-SQL's "KILL <spid>" command?

Thanks in advance,
Christian

2. help: Which small free DB for Windows (NT)?

3. program name and host name in sysprocesses table

4. Blob using SQLPutData: getting Function Sequence Error

5. Win95 logon and Named Pipes

6. Exec Question - How do I do this?

7. Logon failure: unknown user name or bad password

8. Portal configuration error

9. How do i Change the Default Logon Name?

10. Standard SQL Logon account names ????

11. Error 1326 - Logon failure: unknown user name or bad password

12. Logon name problem