How to solve  Error with Oracle: "ORA-01000: maximum open cursors exceeded"

Open cursors

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. 

The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS. 

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session.

 For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. 

 If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more. 

To see max number of cursors:

select a.value, s.username, s.sid, s.serial# 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';

 

To fix the problem increase the maximum number of cursors with this query:

  ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;