My apologies to Jared for hacking up some scripts he sent out ages ago!
Here are some queries to get you info on how many connections you have and
where they come from...
set lines 200
col sessions heading 'SESSIONS'
col username heading 'USERNAME' format a10
col sessions heading 'SESSIONS'
col sid heading 'SID' format 999
col status heading 'STATUS' format a10
col machine format a20 head 'MACHINE'
col client_program format a20 head 'CLIENT PROGRAM'
col server_program format a20 head 'SERVER PROGRAM'
col spid format a8 head 'SRVR|PID'
col serial# format 99999 head 'SERIAL#'
col client_process format 999999 head 'CLIENT|PID'
col osuser format a7
col logon_time format a17 head 'LOGON TIME'
col idle_time format a11 head 'IDLE TIME'
compute sum of sessions on report
- Machines that have 10 or more connections to the database
select machine, count(*) sessions
from v$session
where username is not null
group by
machine
having count(*) >= 10
;
- Total Connections to the database
select count(*)||' Total Connections to the Database'
from v$session
where username is not null
;
- Users who have more than 2 connections to the database.
select username, count(*) sessions
from v$session
where username is not null
group by username
having count(*) > 2
order by sessions, username
;
- Detailed session info for one particular user.
select
to_char(p.spid) spid,
s.username,
s.sid,
s.serial#,
s.status,
s.machine,
to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
- idle time
- days added to hours
--( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
- days separately
substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
- hours
substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
- minutes
substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time,
s.osuser,
substr(s.program,1,20) client_program,
s.process client_process,
substr(p.program,1,20) server_program
from v$session s, v$process p
where s.username is not null
and s.username like upper('%&username%')
and p.addr = s.paddr
order by logon_time, machine
;
Steve Monaghan
Worldcom
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> Lisa_Koivu_at_gelco.com
> Sent: Friday, June 09, 2000 8:47 AM
> To: Multiple recipients of list ORACLE-L
> Subject: concurrent connection tracking
>
>
> Does anyone know of a way to track the number of concurrent
> connections to a
> database?
> For some reason the License High Water Mark in the alert log
> isn't good enough
> - because it tells the truth: at one point one of our databases
> had a bunch of
> runaway processes and there were 591 concurrent connections. My
> boss doesn't
> think that's good enough.
>
> I can't think of a way other than auditing and/or enabling some
> sort of sqlnet
> tracing (please no...)
> or my usual response: write a ksh script to do it for me. I'm
> thinking the
> latter will do fine, but if there is something in Oracle I am not
> aware of I'd
> like to hear of it.
>
> Thanks
> Lisa
> *O*mnipotent *C*at *P*erson
>
>
>
>
> --
> Author:
> INET: Lisa_Koivu_at_gelco.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
Received on Fri Jun 09 2000 - 08:17:22 CDT
