Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sessions numbers

Re: sessions numbers

From: <fitzjarrell_at_cox.net>
Date: 19 Jul 2005 10:11:35 -0700
Message-ID: <1121793095.677064.221660@g49g2000cwa.googlegroups.com>

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 Received on Tue Jul 19 2005 - 12:11:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US