Open Cursor Current by program

set lin 120

set pages 20

col program for a42   

COL max_op_cur FOR 999999 HEADING 'Max. Open C.|per Session'

COL sum_op_cur FOR 999999 HEADING 'Total. Open C.|per Program'

COL avg_op_cur FOR 999999 HEADING 'Avg. Open C.|per Program'

--- Opened Cursors Current

select program,max_op_cur, sum_op_cur, avg_op_cur, sessions

from

(select  s.program, max(t.value) max_op_cur,  sum( t.VALUE) sum_op_cur, round(avg(t.value)) avg_op_cur, count(1) sessions

from v$sesstat t,V$STATNAME n, v$session s

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('opened cursors current') and t.sid=s.sid

group by s.program order by max(t.value) desc )

where rownum<10;

COL PROGRAM FOR A32

COL USERNAME FOR A16

COL MACHINE FOR A24

COL OSUSER FOR A16

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SET PAGES 200

SET LIN 120

 select s.sid, s.username,s.osuser, s.machine,s.logon_time,t.value

from v$sesstat t,V$STATNAME n, v$session s

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('opened cursors current') and t.sid=s.sid

and  s.program='&pProgram' order by t.value desc;

select s.sid, s.username,s.osuser, s.machine,s.logon_time,t.value

from v$sesstat t,V$STATNAME n, v$session s

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('opened cursors current') and t.sid=s.sid

and  s.sid='&&pSID';

---*****************************

---*  By Eini Trujillo. Vzla   *

---*  Apr 2014                 *

---*****************************