Select client IP of a specific session in Oracle Database

You are an Oracle DBA. The most important questions that you have to answer your self when monitoring your Database: "what is the IP address a session is made from". The following is the solution for you.

--# select v$session for logon_time, username (if the session is living)

SELECT s.status, logon_time, s.sid, s.serial#, s.username, s.osuser,

    p.spid as os_server_process_id, s.port os_client_port, s.machine, p.terminal,

    s.program, s.sql_id, s.prev_sql_id --, s.*

FROM v$session s, v$process p

WHERE s.paddr = p.addr

    and s.username='U1'

    and s.machine='WORKGROUP\HOST-5A81BE'

order by status;

--# If your Server OS is Linux you can stop here by using command netstat -napt. You can using the os_server_process_id return from above SQL as the input for the comment.

# netstat -napt | grep <os_server_process_id>

--# session detail (if the session is living)

select * from v$session where sid=5714 and serial# =48901;

--detail of sql statement

select * from v$sql where sql_id='cyhpma1w9s6v1'

--# select program, module, machine, port column from DBA_HIST_ACTIVE_SESS_HISTORY (if the session is terminated)

select T1.program, T1.module, T1.machine, T1.port ,T1.*

from DBA_HIST_ACTIVE_SESS_HISTORY T1

where session_serial# = 48901 and session_id = 5714

    and user_id=(select user_id from dba_users where username='U1')  ;

--select the IP from COMMENT$TEXT of AUD$ table using PORT number, userid and hostname from above sql

select * from sys.aud$

where userid='U1' and terminal like '%HOST-5A81BE%' and ntimestamp#>trunc(sysdate-1)

    and comment$text like '%PORT=49339%';