[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

org.hibernate.exception.GenericJDBCException - Caused by: java.sql.SQLException: ORA-00020: maximum number of processes (50) exceeded

Posted on 2006-10-31
8
Medium Priority
?
9,086 Views
Last Modified: 2012-06-27
Hi

I am getting below exception when I am trying to insert data into database using hibernate.

started
read hibernate.cfg.xml
openSession
Inserting Record
org.hibernate.exception.GenericJDBCException: Cannot open connection
      at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
      at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:327)
      at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:118)
      at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:99)
      at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:73)
      at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:91)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
      at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
      at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
      at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:557)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:545)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:541)
      at HSSFExcelData.insertFunctional(HSSFExcelData.java:127)
      at HSSFExcelData.setData(HSSFExcelData.java:81)
      at HSSFExcelSheet.main(HSSFExcelSheet.java:149)
Caused by: java.sql.SQLException: ORA-00020: maximum number of processes (50) exceeded

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
      at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOsesskey(T4CTTIoauthenticate.java:243)
      at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:298)
      at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:347)
      at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:150)
      at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:549)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:110)
      at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:324)
      ... 15 more
Sheet Index =1

Thanks
0
Comment
Question by:basirana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17845038
>ORA-00020: maximum number of processes (50) exceeded

the message is clear: the number of maximum processes allowed per your oracle database has been reached.
to solve:
* close some processes (ie sessions)
* use shared processes instead of dedicated sessions
* increase the processes parameter of your databases
0
 

Author Comment

by:basirana
ID: 17845104
Below is the code how can I modify. I am closing the session once I insert the record into database.

{

data.setEmpID(ID);
data.setEmpName(Name);
session.save(data);
org.hibernate.Transaction tx = session.beginTransaction();
tx.commit();
session.flush();
session.close();

Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17845147
there are 2 hidden issues:
* web servers usually use connection pooling, hence although you are closing the connection in your code, the connection does not get closed really.
* 50 processes on a oracle database is not really alot for a web-based application. usually you should have rather set like 200-500 or even more, but for sure using shared sessions and not dedicated sessions (is configurable in the tnsnames.ora (client) and / or listener.ora (server) )
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:basirana
ID: 17845225
The senario I am working on is I will be reading the spreadsheet and insert those values to database using hibernate.
I am able to insert up to 38 records without any error

when I try to insert 39th record I am getting below exception later I am getting the exception that I have given above

org.hibernate.exception.GenericJDBCException: Cannot open connection
      at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
      at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:327)
      at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:118)
      at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:99)
      at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:73)
      at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:91)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
      at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
      at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
      at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:557)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:545)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:541)
      at HSSFExcelData.insertFunctional(HSSFExcelData.java:127)
      at HSSFExcelData.setData(HSSFExcelData.java:81)
      at HSSFExcelSheet.main(HSSFExcelSheet.java:150)
Caused by: java.sql.SQLException: ORA-00020: maximum number of processes (50) exceeded

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
      at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOsesskey(T4CTTIoauthenticate.java:243)
      at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:298)
      at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:347)
      at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:150)
      at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:549)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at java.sql.DriverManager.getConnection(Unknown Source)
      at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:110)
      at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:324)
      ... 15 more
Sheet Index =1
0
 

Author Comment

by:basirana
ID: 17845279
How to use SessionFactory for my code. Can you give me some simple example
0
 
LVL 5

Accepted Solution

by:
kannan_ekanath earned 2000 total points
ID: 17847750
I guess you are using a different physical connection. Try,

org.hibernate.Transaction tx = session.beginTransaction();

for(..iterate thru the excel sheet....)
data.setEmpID(ID);
data.setEmpName(Name);
session.save(data);
session.flush();
session.close();
..end of for loop
tx.commit()

Try committing only after inserting all records dont spawn 100 different transactions for 100 records.
If your excel sheet has like a million rows, and you feel you want to commit intermediate results also then in the for loop have a counter and when the counter reaches say 1000 issue a commit.
0
 
LVL 5

Expert Comment

by:kannan_ekanath
ID: 17847754
As an aside, I would you to actually dig through the code

a) IS there any other process that you spawn that uses the database? to me your code looks pretty harmless, you are opening many transactions, but I presume hibernate must be intelligent enough to use the same physical connection (Unless you have done some weird things like using a SingleConnectionDataSource etc)
b) How does your hibernate.cfg.xml look like?
0
 

Author Comment

by:basirana
ID: 17849360
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.save(data);
//org.hibernate.Transaction tx = session.beginTransaction();
//tx.commit();
session.save(data);
session.getTransaction().commit().
session.flush();
session.close();
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question