Error in application log file.
<PRE>SQL_PLSQL_ERROR (ERRNO=604) (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 "APPS.FND_SESSION_UTILITIES", line 37 ORA-06512: at line 1
) (ROUTINE=decryptSessionCookie(String)) 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
No comments:
Post a Comment