Re: Session Ip Address

From: John K. Hinsdale <hin_at_alma.com>
Date: 4 Jan 2007 09:15:29 -0800
Message-ID: <1167930928.245913.24050@51g2000cwl.googlegroups.com>

> Michel Cadot wrote:
> > select sid, machine, utl_inaddr.get_host_address(machine) ip

didn't know about UTL_INADDR -- much easier!

DA Morgan wrote:
> Unfortunately, on Windows machines you are likely to see this:
> ERROR at line 1:
> ORA-29257: host WORKGROUP\PERRITO2 unknown

A simple extension to Michel's query (which does the tricky part) will snip the windows domain/workgroup and try just the host name part;

select sid, machine,

       utl_inaddr.get_host_address(
         decode(instr(machine, '\'),
                0, machine,
                substr(machine, instr(machine, '\')))
       ) AS ip

from v$session
where type='USER' and username is not null

of course there is no guarantee that the Windows machine name is also registered in DNS, but this is the case case at most of my installations.

FYI if the session table is really big these host name resolutions can start to take a really LONG time as they are contacting an external networked service ...

HTH,
JH Received on Thu Jan 04 2007 - 11:15:29 CST