How to fix ‘ORA-01000 maximum open cursors exceeded’ issue in Oracle database

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.

By default, Oracle database allows 300 open_cursors per connection session. Server tries to execute 300 SQL statements per connection by considering the default open_cursors limit. An issue occurs when the open_cursors limit is less than 300 in the Oracle server.

Cause & Workaround.

The reason you receive this error is because Oracle has reached the set limit for open cursors allowed for that executable or that user session. There are two kinds of open cursors: implicit and explicit. Here is some background on how cursors work.

To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information. An IMPLICIT cursor is declared for all data definition and data manipulation statements.
These are internal to Oracle. For queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. You can tune explicit cursors more easily as you can decide when to open them and close them.

Implicit cursors are harder to tune because they are internal to Oracle. If your application is tuned carefully, it may cut down the number of implicit cursors opened.

ORA-01000: "maximum open cursors exceeded"

Cause: 
A host language program attempted to open too many cursors.
The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user session.

Action: 
Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS.       

To check the open_cursors setting for your database (you will need DBA privilege):

SELECT value FROM v$parameter WHERE name = 'open_cursors';

To find the opened cursor in Oracle DB for username – SCOTT

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'
    AND USERNAME     = 'SCOTT';

To resolve this issue , the setting can be updated by using the ALTER SYSTEM command.

alter system set open_cursors = 600 scope=both sid='*';