ORA-01000: Maximum Open Cursors Exceeded

by

The ORA-01000 error indicates that the maximum number of open cursors allowed in the database session has been exceeded. This is controlled by the OPEN_CURSORS initialization parameter, which sets the maximum number of cursors (pointers to SQL statements) a session can have open at once.

Identify Open Cursors: You can use the following query to identify sessions with a large number of open cursors:

1
2
3
4
5
6
SELECT a.value, s.sid, s.serial#, s.username, s.program
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 a.value DESC;
1
2
3
4
5
SQL> 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
----------------   -----------------------
480                         460
1
2
3
4
5
SQL> show parameter open_cursor
 
NAME          TYPE      VALUE
-----     ----------- ------------------------------
open_cursors  integer 460
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.

Dynamically (effective immediately but not persistent):
1
ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

Modifying the SPFILE/PFILE (persistent across restarts):

1
ALTER SYSTEM SET open_cursors = 1000 SCOPE=SPFILE;

Then restart the database to apply the changes if using SPFILE:

1
2
SHUTDOWN IMMEDIATE;
STARTUP;
Issue got fixed !!  :)