ORA-01000: Maximum Open Cursors Exceeded

Buğra PARLAYAN

2 years ago

This error can occur if the Oracle open_cursors parameter is too low or there is a coding issue where sql statements are not closed.

SELECT Max(a.value) AS highest_open_cur, 
       p.value      AS max_open_cur 
FROM   v$sesstat a, 
       v$statname b, 
       v$parameter p 
WHERE  a.statistic# = b.statistic# 
       AND b.name = 'opened cursors current' 
       AND p.name = 'open_cursors' 
GROUP  BY p.value; 

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- -----------------------
       320 300
 
 
 
SQL> show parameter open_cursor
 
NAME				     TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors			     integer 300

SELECT Max(a.value) AS highest_open_cur,

p.value AS max_open_cur

FROM v$sesstat a,

v$statname b,

v$parameter p

WHERE a.statistic# = b.statistic#

AND b.name = 'opened cursors current'

AND p.name = 'open_cursors'

GROUP BY p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR

---------------- -----------------------

320 300

SQL> show parameter open_cursor

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

open_cursors integer 300

Here we can see the highest_open_curosor is more than max_open_cursor. So to avoid this, increase the open_cursor values in spfile. ( default = 300 )

SQL> alter system set open_cursors=5000;
 
System altered.

SQL> alter system set open_cursors=5000;

System altered.