ORA-01000: maximum open cursors exceeded

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