/* You may need to use BB_BB60_STATS SCHEMA if searching for old dates */

SELECT COUNT(*)
  FROM (SELECT USER_PK1
          FROM BB_BB60.ACTIVITY_ACCUMULATOR a
         WHERE TIMESTAMP > TO_DATE('01/02/2007', 'DD/MM/YYYY')
            AND TIMESTAMP < TO_DATE('01/03/2007', 'DD/MM/YYYY')
           AND USER_PK1 IS NOT NULL
        GROUP BY a.session_id);

select c.course_id "Course ID", c.course_name "Course Name",
c.available_ind "Currently Available" from course_main.c left join
activity_accumulator a on c.pk1 = a.course_pk1 where (a.event_type is
null)

select t.teller
,      sum(decode(sign(teller - trunc(last_call_et/60/60)),'0',1,0)) aantal
,      rpad('*',sum(decode(sign(teller - trunc(last_call_et/60/60)),'0',1,0))/5,'*') leuke_sterretjes
from v$session
,    (select rownum-1 teller
      from dba_objects
      where rownum <= (select trunc(max(last_call_et/60/60))+1
                         from v$session where username is not null)
     ) t
where username is not null
and machine like 'bbapp%'
group by t.teller order by 1,2;

select machine
,      to_char(logon_time,'mm/dd/yyyy hh24:mi:ss') Inlogdatum
,      last_call_et Sec_Inactief
,      trunc((sysdate-logon_time)*24)||' H '||trunc(((sysdate-logon_time)-trunc((sysdate-logon_time)*24)/24)*24*60)||' M' aangelogd
,      to_char(trunc(last_call_et/3600,0))||' H '||to_char(trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0))||' M' inactief
from v$session
where username is not null
order by last_call_et desc;

Select username
, sql_text
, count(*) aantal
,trunc(avg(last_call_et)/60) avg_idle_time
,trunc(min(last_call_et)/60) min_idle_time
,trunc(max(last_call_et)/60) max_idle_time
from v$session s
, v$sqlarea a
where a.address = s.PREV_SQL_ADDR
and s.username not in ('SYSTEM','DBSNMP','SYS')
and s.username is not null
group by username,sql_text order by 4 desc;

Bij een “connection leak” geeft een module de connectie niet terug aan de pool. We zien dan in de loop van de tijd voor een bepaald statement het veld “aantal” geleidelijk oplopen,avg_idle is hoog, max_idle is hoog en min_idle wordt 0 iedere keer als de lekkende module gestart wordt. Het sql statement is het laatste statement in de module die de connectie niet terug geeft aan de pool.

select cm.pk1
,      cm.course_name
from bb_bb60.gateway_course_categories gcg
,    (select gc.pk1
      from bb_bb60.gateway_categories gc
      start with gc.pk1 = <pk1_gateway_categories>
      connect by prior gc.pk1 = gc.gatewaycat_pk1
     ) gc1
,    bb_bb60.course_main cm
where gc1.pk1 = gcg.gatewaycat_pk1
and cm.pk1 = gcg.crsmain_pk1
order by cm.course_name

SELECT PFLIO_PK1, COUNT(PFLIO_PK1)

FROM BB_BB60.PFLIO_ITEM

GROUP BY PFLIO_PK1