ORA-01000: maximum open cursors exceeded | How to resolve ORA-01000 error?

ORA-01000: maximum open cursors exceeded :

From last few articles i have explained multiple oracle errors with different kind of examples like ORA-03113 error and Ora-01722 error,ORA-0600 error as well.ORA-01000 is also one of the popular error and most searched error on google.ORA-01000 is searched approximately 6500 times in month.This error is related to cursors,Oracle tries to open too many cursors,the error will come.This error is related to cursor limits and open cursors exceeds the default limit of oracle.To resolve this error we need to now what exactly the open cursor means and how it will impact the database.

“ORA-01000 will be searched approximately 6500 times on google per month.”

Why ORA-01000 error will come?

Situation 1:

Developer/DBA forgets to close cursors.

ORA-01000 error is cursor error.There are lot of cursors in open state as lot of developers and dbas are working on same database.Lot of times the developers missed to close the cursor and cursor will remain in open state.Open cursor take up the space on shared pool.

A cursor is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information

Situation 2 :

When OPEN_CURSOR Parameter limit exceeds.

The DBA needs to set the parameter named  ‘Open_cursors’ to maximum no of cursors open per session.Means if the parameter named OPEN_CURSOR has set to 100 then only 100 cursors will open per session.So If this kind of error occured then DBA and developer need to check the sessions which are inactive and then kill all sessions.

ORA-01000 Error will come then user can not be able to create a single table

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of the Error :

This is also not a simple syntax error so we need to handle this error at server side by setting and changing some parameters.

Solution 1 :

Close all cursors from code.

Developers as well as DBAs needs to check the PL SQL code and close all cursors which are open.

If you are using the statement named ‘dbms_sql.open_cursor()’ then there is need to close that cursor using dbms_sql.close_cursor()

Solution 2:

Choose Appropriate Cache Size

DBA needs to set the cache size as recommended by the oracle.To set the specific cache size DBA needs to set parameter named stmtCacheSize. DBA needs to set this parameter by checking resources available in application.

The default value of the cache size parameter is 30 which will be set by using following statement:

var oracledb = require('oracledb');
oracledb.stmtCacheSize = 30;

Solution 3:

Killing inactive sessions

When this error will occur then try to kill the inactive sessions.When inactive sessions will kill and if all other sessions are into range of open_cursor,then this error will eliminate.

Solution 4 :

Increase the OPEN_CURSOR limit

If this kind of error occured then first try increasing the limit of this attribute.So kindly change the OPEN_CURSOR parameter.Make sure that you are changing the limit of parameter by checking resources available.

You can use following statement to change the parameter limit :

ALTER SYSTEM SET open_cursors = 300 SCOPE=BOTH;

So to resolve this error user needs to set the open_cursor to higher number.But make sure that while changing this parameter check the performance as well as memory of the server.

Hope everyone get the idea about this common error and resolution of the error.Dont forget to share this article with everyone.