Re: find out who is connected to server, How ?

I have actually implemented a script to do just this for SQL*Net V1. Now I can't even take a quarter of the credit because it uses a great free UNIX tool 'lsof' (which I believe is available at uc.berkley, use your favorite archie site). What I do is match up PIDs to INET sockets.

What you can then do is match up the PID to the SPID column of the V$PROCESS table, from this table you get the PID and SERIAL of the user process. .. I wrote a separate script to manage these processes but it is very database specific, I'll look into writing a package to do this if there is significant interest.

• Michael

  ---

#!/bin/perl
## track_dbproc
## Michael Karafotis
 

$ARGS=join(' ',_at_ARGV);
 

open(DPID,"ps aux | grep oracleRTHIRE | grep 'T:I' | grep -v grep | ")

    || die "Problem with ps command\n";  

while (<DPID>) {

    _at_line = split;
    $time{$line[1]}=$line[8];
}
close(DPID);  

open(LSOF,"lsof $ARGS -i TCP:orasrv -a -O | grep -v '\*:orasrv' | sort -t'>' +1
|")

     || die "Problem with lsof command\n";  

print "--------------------------\n";

$cnt=0;

while (<LSOF>) {

    ($command,$pid,$user,$tmp,$tmp,$tmp,$tmp,$tmp,$name,$tmp) = split;     if ($cnt == 0) {

        print "$command\t$pid\tTIME\t$user\t$name\n";     }
    else
    {

        print "$command\t$pid\t$time{$pid}\t$user\t$name\n";     }
 $cnt++;
}  

close(LSOF);  

print "\n";

%% Janos Geller writes:

  jg> DBilodeau (dbilodeau_at_aol.com) wrote: : The ORACLE.INI file on each
  jg> client can contain the line USERNAME=xxx and : this is then
  jg> available by querying the v$session table. I dynamically : build
  jg> the ORACLE.INI and imbed the network user's name in this line
  jg> during : client boot.

  jg> : ..Dave

  jg> True. I've been using this approach (actually, I define the
  jg> USERNAME environment variable under OS/2, but it could be in
  jg> CONFIG.ORA as well) for quite a time now connecting to V6 on
  jg> various platforms. To my surprise, using V7 only SQL*Net V2
  jg> supports this solution, while we are going to have a few TCP/IP
  jg> SQL*Net V1 clients for the foreseeable future. I wonder if Oracle
  jg> could be convinced to support this in SQL*Net V1 -> ORACLE V7 as
  jg> well...

  jg>     - Janos

-- 
______________________________________________________________________________
Michael Karafotis				karafoti_at_pencom.com
Member, Pencom Systems Administration		On-Site: Fidelity FMR Corp
http://www.ari.net/webworks/karafotis/		(617) 563-3692
"The more I look at it, the more I like it ............. I do think its good!"