Checking Status of Currently Open Cursors in ORACLE

Pesky too many open cursors error from ORACLE can be investigated with the help
of the following code:

--total cursors open, by username & machine
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'
and s.machine in ('MY Machine', 'His Machine', 'Her Machine')
group by s.username, s.machine
order by 1 desc;

Visit this nice link for more info