Re: Linking IP address to Oracle process

From: Strategic Data Systems <sds_at_dataplusnet.com>
Date: 1996/11/11
Message-ID: <567m4a$d5c$3@news.inc.net>#1/1

In article <328259C1.32E4_at_bekaert.com>, MARREEL_CHRIS/BENG_at_bekaert.com says...
>
>Andy Finkenstadt wrote:
>>
>> >> We have had a similar problem - using SQL*Net to connect to Oracle, how can
>> >> we tell which session belongs to which user (given that our app is
>> >> designed to connect to Oracle with a common Oracle userid).
>>
>> Using "lsof -i" check for all processes that have connections to
>> the local orasrv port. The remote host name will be the machine
>> connected to your local oracle database.
>>
>> Next cross-check the local process number with the process number
>> recorded in the v$session & v$process views.
>>
>> Andy
>> --
>> Andrew Finkenstadt, The Printing House Ltd, Also a GEnie Sysop
>>
>
>This looks very interresting. Even the guys at the ORACLE-support here in Belgium
>didn't know that because we recently asked this question to the support-department.
>Even the responce center of HP-Belgium told us it was IMPOSSIBLE. I will forward them
>the information.
>
>Where can I find the above mentioned "lsof"-program ?
>I have it on my system (probably installed with other software from a third party) at the
 moment, but I
>don't have any documentation (man-pages....)
>This "lsof"-program seems VERY interresting....
>Can you tell me where I can find it ?
>

I modified the orginal, because the join os v$session and v$process didn't work

accept PID prompt 'Enter PID = '
column username format a8
column sid format 9999
column serial# format 99999999
column sql_text format a45 word_wrap
select

        s.username,
        s.sid,
        s.serial#,
        s.status,
        decode(sql_text, NULL, 'NON-SQL (DDL, GRANT, etc.)', sql_text)

sql_text
from

        v$sqlarea a,
        v$session s,
        v$process p
where
        s.sql_address = a.address(+)
        and s.sql_hash_value = a.hash_value(+)
        and s.username is not null
        /*the following was changed*/
        and s.paddr=p.addr
        and p.spid=&PID
order by
        username

/ Received on Mon Nov 11 1996 - 00:00:00 CST