Re: SQL*Plus Question

"Ed_Zep" <ed_zep_at_ntlworld.com> wrote in message news:1149772435.783220.22890_at_i39g2000cwa.googlegroups.com...
: This should be easy but...
:
: I'm trying to get a list from v$session of users logged in more than
: once with the same username but on different machines.
:
: I can do:
:
: select logon_time, username, osuser, machine, count(1) from v$session
: group by logon_time,username, osuser, machine
: having count(1) > 1
:
: but this will only give me one line per user. I need to see the same
: username, etc for say "JSMITH" 3 times if he's logged in three times on
: different machines...
:
: I'd like to exclude rows where the user is logged on twice on the same
: machine.
:
: Many thanks,
:
: Ed.
:

since you're grouping by logon_time, you'll get one row for each loon_time

in the query that returns the selected population (users logged in with same username on different machines) just include what you need to group by (username) and count on the correct criteria (distinct machine) -- then use that in a subquery to display the data you want to see

ie, something like (not tested):

select ...
from v$sessions
where username in (

    select username
    from v$sessions
    group by username
    having count(distinct machine) > 1
)

++ mcs Received on Thu Jun 08 2006 - 08:34:17 CDT