Re: Session Ip Address

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 4 Jan 2007 09:03:56 -0800
Message-ID: <1167930235.977234.70390@s34g2000cwa.googlegroups.com>

Michel Cadot wrote:
> "Emre" <emre_at_emre.com> a écrit dans le message de news: Xns98AE940DFD6A7emreemrecom_at_fe06.highwinds-media.phx...
> | Hii All
> | How can I see client's ip address from v$session ? or is there another view to see
> | client's ip address
>
> select sid, machine, utl_inaddr.get_host_address(machine) ip
> from v$session
> where type='USER' and username is not null
> /
>
> Regards
> Michel Cadot

Michel,

Very nice solution. However, a small adjustment may be needed in some environments to avoid errors like this when executing the SQL statement that you posted:
ERROR at line 1:

ORA-29257: host DOMAIN\COMPUTER unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

The fix is to simply remove everything before the \, if one exists SELECT
  SID,
  MACHINE, DECODE(INSTR(MACHINE,'\'),0,MACHINE,SUBSTR(MACHINE,INSTR(MACHINE,'\')+1)), UTL_INADDR.GET_HOST_ADDRESS(DECODE(INSTR(MACHINE,'\'),0,MACHINE,SUBSTR(MACHINE,INSTR(MACHINE,'\')+1))) FROM
  V$SESSION
WHERE
  TYPE='USER'; Note that the above could take a very long time to complete.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 04 2007 - 11:03:56 CST