We are facing the maximum open cursor issue [SPR-17001]

[spring-projects-issues]

A V Vamsi krishna opened SPR-17001 and commented

+Error stack trace+

 ** 
|
| |
Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded 

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) 
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) 
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) 
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) 
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) 
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) 
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:220) 
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:48) 
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938) 
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150) 
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) 
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) 
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5631) 
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) 
at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:105) 
at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1134) 
at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1131) 
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1079) |
|

 

---

Reference URL: https://stackoverflow.com/questions/47044033/spring-5-0-1-namedjdbctemplate-batchupdate-ora-01000-maximum-open-cursors-excee

Issue Links:

• #20687 Doc: ParameterMetaData.getParameterType performance on Oracle 12c

[spring-projects-issues]

A V Vamsi krishna commented

Team,

 

Please update us if you are looking for additional details.

[spring-projects-issues]

A V Vamsi krishna commented

Hi Team,

 

Could you please give an update on the above issue.

We are using the 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  in our environment

[spring-projects-issues]

A V Vamsi krishna commented

Hi Team,

Could you treat this as priority. We are facing the issue in our production environment.

 

[spring-projects-issues]

Juergen Hoeller commented

Do the steps in #20687 help:

1. Upgraded Oracle JDBC driver to ojdbc8.jar version 12.2.0.1
2. Create spring.properties with spring.jdbc.getParameterType.ignore=true

There is a corresponding note in our reference documentation since 5.0.2. Beyond that, we are not aware of any remaining issues on our side. It's effectively an Oracle JDBC driver bug with the use of the standard java.sql.ParameterMetaData.getParameterType() API which can be resolved through the steps above.

[spring-projects-issues]

A V Vamsi krishna commented

Hi Juergen,

Thanks for the update. As we mentioned earlier we are using the below Oracle DB version

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit.

Currently we are using ojdbc14. jar version 10.2.0.2.0

Can we use the ojdbc8.jar version 12.2.0.1 in this environment ?

 

[spring-projects-issues]

Juergen Hoeller commented

As far as I know, you can use the Oracle 12 JDBC driver against an Oracle 11 DBMS. However, I'm no expert there; please check with your database administrator.

Even on its own, spring.jdbc.getParameterType.ignore=true should get you pretty far in avoiding that driver bug. You may set that flag in a spring.properties file in the root of your classpath or as a JVM system property.

[spring-projects-issues]

A V Vamsi krishna commented

Hi Juergen,

Currently in our application we are using JDBCTemplate for all DB interactions. For each method call, we are creating new JDBCTemplate, using it and not closing anything (JDBC resources) from application side. Is there any possibility that it may keep Cursors open?.Can we create any number of JDBCTemplate and it will have no impact on number of open cursors count?

Regards,

Ram Lodhi.

[sabinsumghershanan]

@jhoeller I am also facing the issue with open cursors in my application. I am using spring version 5.0.16-RELEASE and ojdbc6 jar and java1.7. The open cursor value has been set to 400 and connction pool value is 40. But still the open cursors get exceeded. Could you please provide some recommendation for the same?

[iKoru]

@jhoeller Me, too. I'm on Spring boot 2.2.5.RELEASE(maybe based on Spring 5.2.6.RELEASE or something) and ojdbc8.jar and java 1.8.

[pkumaravel]

me too facing same issue using Spring JDBC 5.1.3.RELEASE with ojdbc7 driver on a Oracle 12c database, any suggestion or solution.

[sabinsumghershanan]

@pkumaravel I resolved the issue by adding spring.jdbc.getParameterType.ignore=true, as a custom property in was. You can also add it as a spring.properties file in the root of the classpath of your project.