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

Mark Bole <makbo_at_pacbell.net> wrote in message news:<yeyZb.15500$%s5.14364_at_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

Oops, typo.

> 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.

Worked ok for me under hp-ux.

Geez, remember when solaris was a primary platform?

I meant to post some about the VB version for the OP, but he doesn't have support so, oh well.

>
> 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

I tend to vary based on app software, some clients do "interesting" things with machine and osuser, and sites often have non-Oracle people who want to know things like "who is the guilty party?" http://www.jlcomp.demon.co.uk/guilty.html . But it's nice to see someone actually answered the OP.

jg

--
@home.com is bogus. 
I think I voted, but it's kinda hard to tell, all the proof I have is
a sticker that says "I Voted."  Seems to be good for lifting lint.