Troubleshooting Open Cursor Issues

Step 1: Find the session id which opens the maximum current cursors.

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'
order by value desc

Step 2: Find the SQL(s) which may have opened the current cursors.

select count(c.sid) as cnt, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id  -- for 9i and earlier use: c.address=sql.address
and c.sid=786
group by sql.sql_text
order by cnt desc