ORA-01000: maximum open cursors limit exceeded

If you are getting ORA-01000 exception, it means you need to review your design or you need to optimize your code. You need to make sure that all the open cursors are being closed after they are finished being used in application.

To check amount of open cursors in Oracle simple run this query:

select count(*) from v$open_cursor;

To increase/decrease the amount of open cursors limit run the following query:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. High volume web applications.

Only increase the OPEN CURSOR limit if your application really needs it, otherwise focus on releasing the unused cursors in your code. For example, In Java close ResultSet or Statement or Connection object as soon as you are finished with them.