0

I am using hibernate 3 along with spring.My Hibernate configurations are as under :

hibernate.dialect=org.hibernate.dialect.Oracle8iDialect
hibernate.connection.release_mode=on_close

But after starting application, even if only one user accesses it then also I am getting this exception : 

ORA-00020: maximum number of processes (550) exceeded

This is stacktrace:

Caused by: java.sql.SQLException: ORA-00020: maximum number of processes (550) exceeded

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:2037)

I have kept connection pool time out = 5000. I have also tried to found the cause and got that release mode may affect the mechanism of closing DB resources. But I couldn't find exact solution for that. Please help.. Thanks in advance..

Ved
  • 8,577
  • 7
  • 36
  • 69

1 Answers1

0

This is a database error not an application error so you need to go to the database to solve it. 550 processes is a lot more than it sounds so either someone has gone insane or you have a lot of inactive processes running.

The best way to find out is to query the v$session view or Gv$session if you're using a RAC, look at the STATUS column.

Take careful not of where all these sessions are coming from; the OSUSER, TERMINAL and PROGRAM will probably be the most useful. It might almost be worth creating a temporary table with this information - proof and a record afterwards. Then after checking that you're not going to break anything, and with your DBAs if you have any, kill all the inactive sessions simultaneously or one at a time.

That'll remove the error but if it's occurred once it can occur again, so you need to solve it. Either:

  • You've got a lot of people using the database.
  • There is an application / program somewhere that is not closing it's sessions after it's finished.
  • Someone is connecting in the middle of a loop.

Whichever reason it is you need to track it down and correct it. I'd start with the program or terminal from v$session that had the most number of processes.

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • Thanks for your answer.. I will surely check what u suggested.. But I want to figure out 1 thing about the three causes you pointed out, that first one is not possible since even if only 1 person using app then also these error occurs.2nd and 3rd points are worth taking care. thanks again..! – Ved Mar 22 '12 at 10:38
  • @adn_295, just because you're the only person using the app doesn't mean you're the only person using the database. What if someone has connected using a different method? – Ben Mar 22 '12 at 10:42
  • OK, got your point..!! however very less possibility seems for this but still i will chk out – Ved Mar 22 '12 at 10:44