• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle Error: ORA-04031: unable to allocate xxx bytes of shared memory ("","","")

 
Martin Lovell
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working on a web app using Tomcat and Oracle9i on Linux with the thin JDBC drivers.
Occasionally a query will fail and with an SQLException with the message:
ORA-04031: unable to allocate xxx bytes of shared memory ("","","").
The system is really not under much stress when it occurs, but if it is not random, is seems to sometimes be when there are a few complex queries done in a row.
When searching for information about the error, there is some vague information that leads me to believe it might be related to using Statements with dynamic SQL in a few places rather than PreparedStatements. Some information pointed to sorting, and I do use some queries with a long "order by" (6 fields). Some say to increase shared memory in the Oracle configuration, but I have a feeling that would just postpone the problem.
Has anyone run into this problem? Will using PreparedStatements exclusively solve the problem? Could it be related to sorting?
 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where I used to work, we had a web application that used to throw this error quite regularly. Particularly when the site was experiencing heavy load.

As I recall, it was finally tracked down to a memory leak within Oracle itself! Not the driver, but Oracle.

You might enquire on OTN or if you have enough pull, with a Oracle rep. I'm not even sure if it was Oracle that fessed up in the end, or the vendor whose product we were using on Oracle.
 
Kirtikumar Puthran
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We used to get this error on Oracle 8i particularly when our application experienced heavy traffic. Also, there were some procedures which used to do a lot of processing.
Ultimately, we solved this by increasing the initialisation parameter "Shared_Pool_Size".
But, I don't really know, why this occured in the first place. Can anyone throw more light on this ???
Rgds,
Kirtikumar Puthran
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic