Re: user description on console

From: Bob Morrison <rmorrison_at_cahners.com>
Date: 1996/11/18
Message-ID: <3290DBAD.3C87_at_cahners.com>#1/1

Manoj Appully wrote:
>
> Laslop Hans wrote:
> > There are two ways of doing this which depends on the version
> of SQL *Net
>
> 1. If u r using SQL *Net V1, then set the variable
> SQLNET_USERNAME=<username> in the Oracle section of win.ini. So when the
> user connect from such a PC, the shadow/dedicated server process will
> be owned by this user and thus u can determine who the user is, even
> though the same common account is used to logon to the DB.
>
> 2. The other method is if using SQL *Net V2 is to find out the PID
> of the locking process, thru simple sql scripts to query
> v$lock,v$process,v$session and also thru DBA_WAITERS & DBA_BLOCKERS.
> Then run the command lsof | grep <pid>
> this will list the IP address of the client workstation.
> Thus the method in V2 is more convoluted as Oracle has removed a neat
> feature in V1.
>
> Hope this solves ur problem, I am assuming ur Oracle is on a UNIX box.
>
> Manoj Appully

In SQL*Net V2 you can set the USERNAME paramter in the ORACLE section of the oracle.ini file. Then use a sql statement like:

select substr(decode(ses.type,'USER',ses.username,'BACKGROUND', 'Oracle BG',ses.type), 1, 12) "Oracle User"

      ,ses.osuser                   "Client"
      ,prc.spid                     "Spid"
      ,ses.status                   "Status"
      ,ses.program "Program"
from   v$session   ses
      ,v$process   prc

where prc.addr = ses.paddr
and ses.type <> 'BACKGROUND'
order by ses.osuser,to_number(prc.spid);

The ses.osuser column will contain the value of USERNAME.

Bob Morrison Received on Mon Nov 18 1996 - 00:00:00 CET