Re: Session Ip Address

From: John K. Hinsdale <hin_at_alma.com>
Date: 4 Jan 2007 07:14:22 -0800
Message-ID: <1167923661.984773.248240@6g2000cwy.googlegroups.com>

Mark D Powell wrote:

> select sys_context('USERENV','ip_address') from sys.dual;
>
> You could use a database event logon trigger to store the client ip in
> v$session via dbms_application_info or save the data elsewhere where
> the Oracle username logging on is of interest.

... another option is to query the MACHINE column in V_$SESSION and do the DNS resolution to IP address outside Oracle (from perl, Java or whatever). The example Unix shell script below works for me on Linux and Solaris to list the distinct host names and IPs connected to a given Oracle server:

#-------  Cut here ------
#!/bin/csh
# Get machine names connected to Oracle and their IPs
# Replace USER, PASSWD and SID below

echo 'select distinct machine from sys.v_$session;' \ | sqlplus -S USER/PASSWD_at_SID \
| perl -n -e \
'chop; print "$_: ", join(".", unpack('C4', gethostbyname($_))), \ "\n" if ! (m/^MACHINE/) && (m/^[a-z]/i)' #------- Cut here ------

Mark's solution is far easier if all you need is the IP for the current session, as opposed to
all the IPs connected across the entire instance.

Cheers,

JH Received on Thu Jan 04 2007 - 09:14:22 CST