ORA-01000: maximum open cursors exceeded

 Error in application log file.

<PRE>SQL_PLSQL_ERROR &#40;ERRNO=604&#41; &#40;REASON=java.sql.SQLException: ORA-00604: error occurred at recursive 

SQL level 1 ORA-01000: maximum open cursors exceeded ORA-00604: 

error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded ORA-01000: maximum open cursors 

exceeded ORA-06512: at &#34;APPS.FND_SESSION_UTILITIES&#34;, line 37 ORA-06512: at line 1

 &#41; &#40;ROUTINE=decryptSessionCookie&#40;String&#41;&#41; ICX_SESSION_FAILED </PRE>

Servlet error: An exception occurred. The current application deployment descriptors do not allow for 

including it in this response. Please consult the application log for details.

                                        Solution

To troubleshoot the open cursors issue:

Login to the SYS schema (or any schema with DBA privilege) of the database.

Find out the session that is causing the error by using the following SQL statement:

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

              600                                                 600

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

VALUE

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

600

Increase open_cursors value 600 to 1000.

LOgin with SYSDBA

$ sqlplus / as sysdba

SQL> alter system set open_cursors = 1000 scope=both;

System altered.

SQL> commit;

Commit complete.

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

VALUE

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

1000

SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size                  2160752 bytes

Variable Size            1677723536 bytes

Database Buffers         5771362304 bytes

Redo Buffers               31379456 bytes

Database mounted.

Database opened.

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

VALUE

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

1000

Find which SQL is using more cursors

SQL> 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 s.username is not null;

     VALUE USERNAME               SID    SERIAL#

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

        47 APPS                                  358        134

        63 APPS                                  365        160

        88 APPS                                  453         12

        51 APPS                                  459         17

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

             102                                                     1000