cannot get oracle connection

peiguo · **Joined:** Mon Jun 16, 2008 1:15 pm **Posts:** 15

Short Description:
Oracle throws ora-00604 and ora-04031, which sort of indicating that oracle used up its shared pool, but what made it really interesting is that:

1) There was no other operation competing for resources at that point (at least based on jboss log, and I assume that nothing other than so and so project runs on ts3).

2) Also this was the very first thing happened this morning, according to JBoss log. The last thing on Friday was successful, and TS3 was idle through the weekend. (What happened was that JBoss/Hibernate, was trying to grab a connection, so it could prepare a statement, but it couldn’t get any connection.)

Hibernate version: Default that came with JBoss 4.2.2

Mapping documents:n/a

Code between sessionFactory.openSession() and session.close():n/a

Full stack trace of any exception that occurs:

Code:

[org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select distinct(-privilege#)...","sga heap(1,0)","kglsim heap")

Name and version of the database you are using:oracle 11g

The generated SQL (show_sql=true):

Code:

from SavedItemSearch as sis                              

           where (sis.allUsers = '1' or sis.personCreated = :personCreated

cbredesen · **Posted:** Mon Dec 22, 2008 6:05 pm

Any time I see "recursive" I figure that SGA might be in jeopardy. Same with Java stack space & recursive calls. Can you post the SQL generated, rather than the HQL?

peiguo · **Joined:** Mon Jun 16, 2008 1:15 pm **Posts:** 15

cbredesen wrote:

Any time I see "recursive" I figure that SGA might be in jeopardy. Same with Java stack space & recursive calls. Can you post the SQL generated, rather than the HQL?

Here is the sql query, and it is rather simple:

Code:

select * from saved_item_searches sis

where (sis.all_users ='1' or sis.person_created =:person_created)

;

cbredesen · **Posted:** Mon Dec 22, 2008 9:18 pm

That's not generated by Hibernate. Turn on debug logging for org.hibernate.SQL in log4j or enable the property hibernate.show_sql=true.