Re: Users with same name

From: Alex Heney <heneya_at_entcf3.agw.co.bt.uk>
Date: 1996/12/12
Message-ID: <32AFCF50.1A56_at_entcf3.agw.co.bt.uk>#1/1

tsmail_at_public.bta.net.cn wrote:
>
> We are developing an application using Oracle and, for some reason, all
> the developers have to use the same Oracle username, just like
> "developer". During the process of coding, we often get some trouble from
> deadlock and need to kill the session that holds the lock. When we use
> the Session Monitor in SQL*DBA we find all the usernames to be the same,
> so although we know which client PC is the locker, we don't know which
> session is the relative one.
> Is there any way that can distinguish the client PCs with the same Oracle
> username? We have Oracle V7.2 and the environment of client PC is
> Windows3.1.
>
> Thanks in Advance.
>
> Frank Feng
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

We have a similar situation, in that all our live users use the same username, and we need to know which session is causing problems on occasion (e.g. one PC has for some unknown reason just locked up). What we have done is the following:

1. In user SYS (Not SYSTEM), run the following two SQL statements (where xxxxx is replaced by the appropriate user name).

		CREATE SYNONYM xxxxx.user_sessions for v$session;
                GRANT select ON v_$session to xxxxx;

	2. In each Form, we use the following cursor in a

WHEN-NEW-FORM-INSTANCE trigger. This cursor is OPENED and FETCHED into a display field showing the session ID on screen.

		CURSOR sid_cur IS
		SELECT sid  FROM user_sessions
		WHERE audsid = (SELECT userenv('SESSIONID') FROM dual);

-- 
The above posting represents the personal opinions of the author and
is not to be taken as official (or unofficial) policy or opinions of 
his employer.

Alex Heney, Living in the Global Village.

Received on Thu Dec 12 1996 - 00:00:00 CET