Spring Boot Application using Oracle - ORA-01000: maximum open cursors exceeded - error happening during load testing

Am working on spring-boot application. While performing load testing we get error

"ORA-01000:maximum open cursors exceeded"

We have following entries for spring boot and jdbc in the pom.xml file

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.6.RELEASE</version>
</parent>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>

To fix the issue I tried changing the "ojdbc" from 6 to 8.

<dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

but I still got the same issue.

All our backend calls using either "jdbcTemplate" or "namedParameterJdbcTemplate".

The error is happening during load testing.
Our Oracle DB cursor limit is 1000.
Load test run game the error after 1hr 55mins and failure rate our our service was 0.5%
the number of users/threads is set to 25.

Looking for some suggestions to fix this issue.

My first suggestion would be to upgrade the update Spring Library including Spring JDBC and JPA version to latest. The earlier spring JPA version was not closing the cursors properly.

This problem occurs commonly because of not cleaning up resultsets, statements or connections. Each ResultSet that you create, uses a cursor on the backend. If you never close the ResultSet, the Statement that created it or the Connection that was used for the statement, those cursors never get closed. As you are using a connection pool, the connections are never physically closed, thus the cursors are never closed either.

What causes the Solve ora-01000 maximum open cursors exceeded java.sql.SQLException problem in JDBC java-

1. Not closing the JDBC Statement object can cause maximum open cursors
   exceeded java.sql.SQLException,
2. Not closing the JDBC PreparedStatement object can cause maximum open
   cursors exceeded java.sql.SQLException,
3. Not closing the JDBC CallableStatement object can cause maximum open
   cursors exceeded java.sql.SQLException,
4. Not closing the JDBC ResultSet object and Not closing the JDBC
   Connections object can cause maximum open cursors exceeded
   java.sql.SQLException

Does Spring JdbcTemplate Close Connections? … Not Always.

Decent developers usually know that they have to try/catch/finally to ensure they clean up connections, file handles, or any number of things. But then, for Java, you hear “just use JdbcTemplate! it does all this boilerplate for you!”. I had, for the longest time, assumed that JdbcTemplate would clean up connections in addition to results sets. In fact, you’ll see this online a lot. But be careful! This does not appear to be the case, or if it is, it is at least data source dependent… and that actually makes sense if you think about their purpose.

When you don't have a Spring managed transaction then yes the JdbcTemplate will call the close() method on the Connection. However if there was already a connection available due to Springs transaction management closing the connection will be handled by Springs transaction support, which in turn also will call close() on the Connection.

EDIT:

Workaround:

Increase the maximum open cursor in the database by executing the following SQL command on the database:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

This example sets the maximum open cursors to 1000. Change this value as required.

Resolution:

Update the Oracle JDBC Driver to the latest version (12.2.0.1)

There is a new version of the Oracle JDBC driver which fixes the cursor leak.

Okay, I finally got an answer. My project was EXACTLY same as yours(Spring boot 2.1.6.RELEASE and ojdbc8 12.2.0.1), (additionally Spring data jpa,) and upgrading version to 2.2.5.RELEASE didn't work for me.

I had the same exception occurs when every time I call the same function using certain amount of data(or above) - I tried to get rid of this error by upgrading ojdbc version to 19.6.0.0, adding spring config spring.jdbc.getParameterType.ignore, and so on. None of them works for me.

Finally what I found was the return type of jpa repository method. Originally my method was like this :

Stream<MyEntity> findByMyColumn(MyColumnType myColumnValue);

note that the return type of this repository method is java.util.Stream. The reason why I get this return type as stream is that this method is used only when the streaming is required.
And everytime I execute same procedure, the stack trace contains this method and I feel weird.
So I changed the type to List, and the error is GONE!!!

List<MyEntity> findByMyColumn(MyColumnType myColumnValue);

I think that the Stream is holding cursors even if the stream ended. Obviously it's a bug on Spring boot or ojdbc, but none of them seems to recognize or have willing to fix this problem.

Hope that helps to your situation, too.

ok, this took long for me to come back and post.
So for me this issue got resolbved just by having following entry in pom.xml

<dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

and for sometime when it didnt work we realized there was another ojdbc7 which was getting picked up by the environment and it was getting introduced due to some other dependency. So once we suppressed that the ojdbc8 got picked up and our issue got resolved.

Upgrading to the latest oracle jdbc driver solved the problem. We upgraded to : version (12.2.0.1)