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

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
basiranaAsked:
Who is Participating?
 
kannan_ekanathConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
basiranaAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
basiranaAuthor Commented:
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
 
basiranaAuthor Commented:
How to use SessionFactory for my code. Can you give me some simple example
0
 
kannan_ekanathCommented:
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
 
basiranaAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.