Thursday, May 14, 2015

ORA-01000: maximum open cursors exceeded

The OPEN_CURSORS parameter is set to the maximum number of cursors that each session can open at a time.
Example, if the value of OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

If the number of cursors in the database exceeds the maximum limit then you will get the following error:

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded.

Cause : The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors.
compare the parent and child table rows, if both table rows are not matched then you may get this error
further you can check the following steps:

Login to the schema with DBA privilege of the database.

To find out the session that is causing the error using the below SQL statement:

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;

you will get the output as details of all sessions. You can see the maximum out session IDs.

you can see the sql_text and session_id by run the following SQL statement which are causing maxing out of open cursors,

SELECT  SID ,SQL_TEXT, COUNT(*) AS "OPEN CURSORS", USER_NAME FROM V$OPEN_CURSOR GROUP BY SID ,SQL_TEXT ;  OR
SELECT  SID ,SQL_TEXT, COUNT(*) AS "OPEN CURSORS", USER_NAME FROM V$OPEN_CURSOR WHERE SID IN ($SID);($SID = SESIONID IF YOU KNOW)

If some code is running with above SQL queries, then check that Java Statement, Resultset, or connection are closing properly or not.

To verify the maximum OPEN_CURSORS parameter run the following sql statement, or monitor v$sesstat for the maximum opened cursors current:

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;

If your sessions are running close to the limit, then increase the value of the OPEN_CURSORS parameter by running the following sql statement and restart Oracle:

ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH;

No comments:

Post a Comment

Thanks for giving comments!!