Open source scripts for Oracle health check

Do you have the latest patches for oracle installed? make sure you have patch 4547817

Try this:

--total cursors open, 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';

I'm not sure but it should be live data.

From: http://www.orafaq.com/node/758