[Okta Webinar] Learn how to a build a cloud-first strategyRegister 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,091 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

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.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
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:
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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