ORA-01000: maximum open cursors exceeded

Many times we may see this error. This means we have crossed the limit of open cursor. You can check the value assigned for cursor

show parameter open_cursor

Solution 1:

Increase the value for open_cursor

ALTER SYSTEM SET OPEN_CURSORS=500 SID='*' SCOPE=BOTH;


Solution 2:

Tune the sql statement to limit it for creating multiple sessions. Below sql will help you to check the number of cursor open by sessions.


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 cursor '
group by s.username, s.machine order by 1 desc;