SCRIPT:Sql to Check Open Cursors in Oracle Database

Total Open Cursors By Session:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

Total Open Cursors By username & HostMachine:
select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;

MAX allowed Cursors and total open Cursors:
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

--Session Cached Cursor's :

select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;

---To get the % useage of all available open cursor

SELECT 'session_cached_cursors' parameter, LPAD(value, 5) value,
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) usage
FROM ( SELECT MAX(s.value) used
FROM v$statname n, v$sesstat s
WHERE
n.name = 'session cursor cache count' and
s.statistic# = n.statistic# ),
(SELECT value
FROM v$parameter
WHERE
name = 'session_cached_cursors' )
UNION ALL
SELECT 'open_cursors', LPAD(value, 5), to_char(100 * used / value, '990') || '%'
FROM
(SELECT MAX(sum(s.value)) used
FROM v$statname n, v$sesstat s
WHERE
n.name in ('opened cursors current', 'session cursor cache count') and s.statistic#=n.statistic#
GROUP BY
s.sid
),
(SELECT value
FROM v$parameter
WHERE
name = 'open_cursors' )
/