OraFAQ Forum: SQL & PL/SQL » IP Address of Users

Home » SQL & PL/SQL » SQL & PL/SQL » IP Address of Users

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

IP Address of Users [message #230215]

Wed, 11 April 2007 04:13

atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai

Member

Hi Experts,

I need the table name in Oracle which stores the
IP address of the Users which access the Oracle
Database or Application.

Thnx
Atul

Report message to a moderator

Re: IP Address of Users [message #230231 is a reply to message #230215]

Wed, 11 April 2007 04:38

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

select sid, machine, utl_inaddr.get_host_address(machine) ip 
from v$session 
where type='USER' and username is not null 
/

Regards
Michel

Report message to a moderator

Re: IP Address of Users [message #230241 is a reply to message #230231]

Wed, 11 April 2007 04:53

atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai

Member

Thnx for the Reply..

When i run this query..
i am getting the error message

select sid, machine, utl_inaddr.get_host_address(stgsunin2) ip
from v$session
where type='USER' and username is not null
/

ORA-00904: "STGSUNIN2": invalid identifier

Report message to a moderator

Re: IP Address of Users [message #230242 is a reply to message #230241]

Wed, 11 April 2007 04:55

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

machine is a column of v$session.
stgsunin2 is not.

If you know the name use:
select utl_inaddr.get_host_address('stgsunin2') from dual;

The previous query gives you all the machine sand IP of your clients.

Regards
Michel

Report message to a moderator

Re: IP Address of Users [message #230250 is a reply to message #230215]

Wed, 11 April 2007 05:18

atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai

Member

When i run this query i am getting this error

SELECT SID, MACHINE,USERNAME,OSUSER,SERVER,PROGRAM,MODULE,
UTL_INADDR.GET_HOST_ADDRESS(substr(machine,11,7)) IP_ADDRESS
FROM V$SESSION
WHERE TYPE='USER'
AND USERNAME IS NOT NULL

ORA-29257: host KSPL294 unknown
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

the package UTL_INADDR is in wrapped mode so i cant debug

Report message to a moderator