Re: Session Ip Address

"DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 1167930113.861103_at_bubbleator.drizzle.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
|
| Unfortunately, on Windows machines you are likely to see this:
|
| SQL> select sid, machine, utl_inaddr.get_host_address(machine) ip
| 2 from v$session
| 3 where type='USER' and username is not null
| 4 /
| select sid, machine, utl_inaddr.get_host_address(machine) ip
| *
| ERROR at line 1:
| ORA-29257: host WORKGROUP\PERRITO2 unknown
| ORA-06512: at "SYS.UTL_INADDR", line 19
| ORA-06512: at "SYS.UTL_INADDR", line 40
| ORA-06512: at line 1
|
|
| SQL> select sid, machine
| 2 from v$session
| 3 where type='USER' and username is not null;
|
| SID MACHINE
| ---------- ------------------------------------------------------
| 158 WORKGROUP\PERRITO2
|
| SQL> select utl_inaddr.get_host_address(machine)
| 2 from dual;
| select utl_inaddr.get_host_address(machine)
| *
| ERROR at line 1:
| ORA-00904: "MACHINE": invalid identifier
|
| SQL>
| --
| Daniel A. Morgan
| University of Washington
| damorgan_at_x.washington.edu
| (replace x with u to respond)
| Puget Sound Oracle Users Group
| www.psoug.org

Quite easy to fix:

select sid, machine,

       UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1)) ip from v$session
where type='USER' and username is not null /

Regards
Michel Cadot Received on Thu Jan 04 2007 - 11:30:26 CST