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%';
