Re: sessions numbers
- From: fitzjarrell@xxxxxxx
- Date: 19 Jul 2005 10:11:35 -0700
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);
end;
/
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
.
- References:
- sessions numbers
- From: Tom
- sessions numbers
- Prev by Date: Re: Most performant way to code client script.
- Next by Date: Re: Database Restore
- Previous by thread: Re: sessions numbers
- Next by thread: Pls correct SQL. Urgent
- Index(es):
Relevant Pages
|
