Home » SQL & PL/SQL » SQL & PL/SQL » IP Address of Users
IP Address of Users [message #230215] Wed, 11 April 2007 04:13 Go to next message
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
Re: IP Address of Users [message #230231 is a reply to message #230215] Wed, 11 April 2007 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: IP Address of Users [message #230241 is a reply to message #230231] Wed, 11 April 2007 04:53 Go to previous messageGo to next message
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
Re: IP Address of Users [message #230242 is a reply to message #230241] Wed, 11 April 2007 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: IP Address of Users [message #230250 is a reply to message #230215] Wed, 11 April 2007 05:18 Go to previous messageGo to next message
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
Re: IP Address of Users [message #230251 is a reply to message #230215] Wed, 11 April 2007 05:20 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
machine names are stored in the V$Session in this manner

KEYDOMAIN\KSPL312
Re: IP Address of Users [message #230263 is a reply to message #230251] Wed, 11 April 2007 06:08 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ORA-29257: host KSPL294 unknown


It seems the name resolution of the Oracle server doesn't know a host "KSPL294", so check the DSN configuration of the Oracle server



Previous Topic: Zip file
Next Topic: ORA-01537 when creating tablespace
Goto Forum:
  


Current Time: Tue Dec 06 00:02:16 CST 2016

Total time taken to generate the page: 0.08200 seconds