How to find IP Address of Clients
I am useing oracle 8i on sun solaris and clients are of windows
when quering v$session it gives many details but does not gives the IP_Address of the clients.
How to get the IP_Address of the clients.
SELECT sys_context('USERENV', 'OS_USER') FROM dual;
If the client uses a middle tier then the ip address of the middle tier is returned.
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') "My IP Address" from dual;
For more details refer to Metalink Note: 120797.1
Anyone can hold the helm when the sea is calm.
TRY THIS. IT WILL GIVE YOU THE ENTIRE LIST OF IP'S FOR ALL LOGONS
select AU.userid ,
substr(AU.comment$text, 68,21) as "Host / IP_ADDRESS"
from sys.aud$ AU
where to_char(AU.timestamp#, 'DD-MON-YY') IN ('24-APR-06')
This one may be better:
select /*+ ROWID (sys.user_audit_session) */
substr(AU.comment$text, 68,21) as "Host / IP_ADDRESS",
from sys.aud$ AU, sys.DBA_AUDIT_TRAIL UA,
where AU.action# = '100' and
to_char(AU.timestamp#, 'DD-MON-YY') IN ('24-APR-06')
and AU.sessionid = UA.sessionid
order by timestamp DESC
Thanks for your replies.
But I am not able to find any records in table sys.aus$.
I had logged in as sys also but still shows "no records found"
Also SYS_CONTEXT('USERENV','IP_ADDRESS') will give the IP_Address of the current user not all the users connected.
You can try simply
select sys_context('username' ,'ip_address')
for the sys.aud$ queries to work you shoud
audit_trail=DB in the init.ora and then bounce the database
Last edited by hrishy; 04-25-2006 at 04:41 AM.
Click Here to Expand Forum to Full Width