First, assume your schema is called ‘D’.
To see the cursor parameters:
<pre lang="sql">
show parameter cursor;
See this faq for the meaning of these parameters: http://www.orafaq.com/node/758
To check the amount of “open” and “closeable” run:
<pre lang="sql">
select a.value, s.username, s.sid
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 username = 'D';
To find the session which is having the largest number of cursors:
<pre lang="sql">
select s.username, s.sid, a.value
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.username = 'D'
and a.value = (select max(a.value) 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.username = 'D');
To find the sql attached to the cursors:
<pre lang="sql">
SELECT oc.sql_text, s.sid
FROM v$open_cursor oc, v$session s
WHERE OC.sid = S.sid
AND OC.USER_NAME ='D'
AND s.sid=123