RE: Concurrent Users

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Wed, 29 Nov 2000 10:50:24 -0600
Message-Id: <10695.123211@fatcity.com>

I use the following two scripts. One for V7 and one for V8. There is a little more to it than what is here but this will give you the general idea. The scripts look for distinct logins and eliminate the same person being logged in more than once. I load them to a table and then produce a weekly report. We found the V$License numbers were inaccurate due to some applications using multiple sessions for the same user.

V7 Script:

set pause off
SET ECHO off
set verify off
set feedback off
define SERVER = &1
define DBLINK = &2

insert into test_user.km_highwater
(select distinct to_char(to_date(value,'J')) SD, sysdate, '&SERVER'a,
db.name, username,
osuser, machine

         from v$session@&DBLINK, v$instance@&DBLINK, v$database@&DBLINK db
                 where key='STARTUP TIME - JULIAN');

exit;

V8 Script:

set pause off
SET ECHO off
set verify off
set feedback off
define SERVER = &1
define DBLINK = &2

insert into test_user.km_highwater
(select distinct startup_time, sysdate, '&SERVER'a, db.name, username,

osuser, machine

         from v$session@&DBLINK, v$instance@&DBLINK, v$database@&DBLINK db); exit;

Reporting Script:
set verify off
set pagesize 60
set linesize 132
set feedback off;
col sample_date format a20;
spool highwater_rpt2.txt
TTITLE LEFT 'Highwater Report by Server - Instance' Skip 2; column counter format 999
compute sum label 'TOTAL' of max_users on report break on report on server skip 1 on instance_name select server, instance_name, max(max_users) "Max_Users" from

        (select server, instance_name, sample_date, sum(counter) max_users from

                (select distinct sample_date, server, instance_name,
user_name, os_user, terminal, count(*) counter
                        from km_highwater 
                        where (instance_name in

('FINPROD1','FINLEG01','OGPROD') and os_user <> 'applmgr' and os_user <>
'oracle')

                        or (instance_name = 'PEU1' and os_user <> 'applmgr'
)
                        or (instance_name not in


('FINPROD1','FINLEG01','PEU1','OGPROD') and os_user not like '%oracle%')

                        group by server, instance_name, sample_date,
user_name, os_user, terminal)
        group by server, instance_name, sample_date)

group by server, instance_name;
spool off;
exit;

Ron Smith
Database Administration
rlsmith_at_kmg.com

-----Original Message-----
From: Emine ATES [mailto:emineates_at_postmaster.co.uk] Sent: Wednesday, November 29, 2000 10:06 AM To: Multiple recipients of list ORACLE-L Subject: Re: Concurrent Users

I think that you should write a script for your specific need, but you can use following query if you want. "select sessions_max,sessions_current,sessions_highwater from v$license;"
bye
On Wed, 29 Nov 2000 07:01:45 -0800 "Browning, Alan" <abrowning_at_moorman.com> wrote:

> I am trying to track the number of concurrent users on our database
> for a given period of time.  We are an HP-UX shop running on 7.3.4.5.0.
> 
> Any help would greatly be appreciated!
> 
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Browning, Alan
>   INET: abrowning_at_moorman.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Emine ATES
  INET: emineates_at_postmaster.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

Received on Wed Nov 29 2000 - 10:50:24 CST