Re: How to get the information like IP address,host name etc of connected clients via V$views

From: Mark Bole <makbo_at_pacbell.net>
Date: Sat, 21 Feb 2004 01:10:54 GMT
Message-ID: <yeyZb.15500$%s5.14364@newssvr29.news.prodigy.com>

Joel Garry wrote:

> dt_146_at_yahoo.co.in (David) wrote in message news:<d5840ebd.0402200807.27b8589d_at_posting.google.com>...
>

>>Hi Every one,
>>
>>I want to get the information like (IP Address,Hostname,Active
>>sessions , Database Server?.etc) of the connected clients
>>via V$Views (ORACLE).

[...]
>
> If you have support, get on metalink and look at their PDA tool. Very
> intersting, especially the unix version which is all shell scripts
> that make pretty html output.
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=139597.1
>
> Also check out the top tech docs link, which can be drilled down to
> some very informative scripts.
>
> jg

[...]

I think you meant RDA, "remote diagnostic assistant". I for one have not gotten it to work under Solaris 8 due to some silly bug regarding the case of the ORACLE_SID environment variable. And ironically, you can't open a TAR with Oracle about a bug in one of their primary TAR-busting tools.

The sys_context and event attributes tools previously suggested look at a single session from within that session, if I read the docs correctly. (But what great tools they are for that!)

To respond to the OP's question about V$ views, if you want a "big picture" look at all connected clients at a point in time, you can use many of the columns of the V$PROCESS and V$SESSION views.

The following example will give you process IDs, active vs. inactive, hostnames (=> ip address), etc. of all connected clients.

select

     vs.sid,
     vp.spid,
     vs.username,
     vs.status,
     to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') time,
     vs.machine,
     vs.osuser,
     vs.process
from
     v$session vs,
     v$process vp
where
       vs.username is not null

   and vs.paddr = vp.addr

--Mark Bole Received on Fri Feb 20 2004 - 19:10:54 CST