Getting java.sql.SQLException: ORA-04031:

Question

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

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