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?
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