Hi getting java.sql.SQLException: ORA-04031:

Question

Hi I am getting Following when query executing on through web application

java.sql.SQLException: ORA-04031:
java.sql.SQLException: ORA-04031: unable to allocate 48784 bytes of shared memory ("shared pool","SELECT   emplid levempid, '2...","Typecheck","qry_text : qcpisqt")

But same query is executing through TOAD properly.

Answer 1

Found a similar question on oracle forum, and the solution that seemed to have work is:

The user didn't have server=dedicated in their tnsnames entry. Adding this line in made the query work.

Hope this helps.

Answer 2

Hi Yashwant,

Chances are very high that your Java code is not making use of bind variables. In that case each SQL statement is unique and will not be reused, thrashing the shared pool. It will become fragmented and finally results in the ORA-04031.

Restarting the database will only temporarily work, but eventually you will run into the same problems. Increasing the shared pool size and regularly restarting the database is not a real solution. Only real solution is to rewrite your SQL to use bind variables.

Here is an AskTom thread of someone experiencing something similar: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:528893984337

And a nice little program to track which SQL statements are not making use of bind variables can be found in this thread: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1163635055580

Hope this helps.

Regards, Rob.

Answer 3

In metalink,they state this as :

The ORA-04031 error is usually due to fragmentation in the library cache or shared pool reserved space. Before of increasing the shared pool size consider to tune the application to use shared sql and tune SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, and SHARED_POOL_RESERVED_MIN_ALLOC. First determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by issuing the following query:

SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved; The ORA-04031 is a result of lack of contiguous space in the shared pool reserved space if: REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC.

To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.

The ORA-04031 is a result of lack of contiguous space in the library cache if:

REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

or

REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

The first step would be to consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to put more objects into the shared pool reserved space and increase SHARED_POOL_SIZE......