| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: sessions numbers
Tom wrote:
> RHEL 3
> 9.2.0.4
>
> I currently log into a file the number of connected users at a given
> time with
>
> select username, count(*) from v$session group by username;
>
> Is there anyway to include session numbers into this also? eg each user
> has x sessions?
>
> thanks!
I don't understand why your query isn't returning such information, as you have it written to provide such data:
USERNAME COUNT(*)
------------------------------ ----------
ETSDEV 2
SYS 1
7
Your count(*), from v$session, provides the number of sessions each username has. Why do you need sessionid with that? What purpose does it serve?
SQL> select username, sid, count(*)
2 from v$session
3 group by username, sid;
USERNAME SID COUNT(*)
------------------------------ ---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
SYS 8 1
ETSDEV 9 1
ETSDEV 11 1
10 rows selected.
You end up with each username listed for each session they hold, each with a count of 1 (since SID is included in the grouping, and it's unique in conjunction with the username), unlike your original output which listed distinct usernames (by virtue of the group by) and a total of the sessions for each user. And, it sounds as though what you really want is your original output. But, if you DO want such output then a little 'magic' on the part of SQL*Plus is in order:
SQL> break on username skip 1
SQL> select username, sid, count(*)
2 from v$session
3 group by username, sid;
USERNAME SID COUNT(*)
------------------------------ ---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
SYS 8 1
ETSDEV 9 1
11 1
10 rows selected.
Although I still fail to see what the SID is going to provide you, as you need both the SID and SERIAL# to identify the session. Possibly this is what you want:
SQL> select username, sid, serial#
2 from v$session;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
SYS 8 114
ETSDEV 9 9
11 11
10 rows selected.
Or, possibly, SQL*Plus won't provide what you want and, thus, you need to move to PL/SQL:
declare
cursor get_user_sess is
select username, sid, serial#
from v$session
where username is not null;
ucount number:=0;
prevuser v$session.username%type:=NULL;
outvar varchar2(200);
begin
for sessrec in get_user_sess loop
if prevuser is null then
ucount := 1;
prevuser := sessrec.username;
outvar := sessrec.username||'
('||sessrec.sid||','||sessrec.serial#||')';
elsif prevuser = sessrec.username then
ucount := ucount + 1;
prevuser := sessrec.username;
outvar := outvar||'
'||sessrec.sid||','||sessrec.serial#||')';
elsif prevuser <> sessrec.username then
outvar := outvar ||' sessions: '||ucount;
dbms_output.put_line(outvar);
ucount := 1;
prevuser := sessrec.username;
outvar := sessrec.username||'
('||sessrec.sid||','||sessrec.serial#||')';
end if;
end loop;
outvar := outvar ||' sessions: '||ucount;
dbms_output.put_line(outvar);
SQL> @sessions
SYS (8,114) sessions: 1
ETSDEV (9,9) (11,11) sessions: 2
PL/SQL procedure successfully completed.
SQL> Possibly the examples I've provided will prod you into being a clearer on what you really want.
David Fitzjarrell Received on Tue Jul 19 2005 - 12:11:35 CDT
 |
 |