Solved

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

Posted on 2006-10-31
8
9,034 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:basirana
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

Accepted Solution

by:
kannan_ekanath earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
computer science syllabus 3 52
word0 challenge 3 56
Eclipse Neon and jdk 1.8.0 11 65
what is a "java.lang.System Property"   ? 20 35
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now