ORA-04031: unable to allocate 4128 bytes of shared

[dngaya]

hi,
I have a database oracle 8.1.7 on HP Unix .two users connected
on the database and launched the requests,
they obtained the error below one used TOAD and the other another software.
here the error message:

the following error has occurred: ORA-04031: unable to allocate 4128 bytes of shared
memory ("shared pool","unknown object","sga heap","multiblock rea").

thank you to give me the ideas on the way of to resolve this problem.
thank you very much.

[Hari Kumar]

If the error Caused by Oracle

1. If you are using with 8.1.7, then there is a memory leak in that and u have to apply patch for that .You can upgrade it to 8.1.7.2
or above.......

2. Increase the SHARED_POOL_SIZE
You need to change SHARED_POOL_SIZE because the default tends to be a low
estimate when utilizing the procedural option. As discussed earlier, one
needs to shutdown/startup when changing the SHARED_POOL_SIZE

Diagnostic tools :
1.The V$SHARED_POOL _RESERVED dictionary view

2.The supplied package and procedure:
DBMS_SHARED_POOL
ABORTED_REQUEST_THRESHOLD
Guidelines: Set the parameter SHARED_POOL_RESERVED_SIZE
The explanation of some of the Coloumns of V$SHARED_POOL_RESERVED is as follows -------
REQUEST_FAILURES
NUMBER
Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-4031 occurred)

LAST_FAILURE_SIZE
NUMBER
Request size of the last failed request (that is, the request size for the last ORA-4031 error)

ABORTED_REQUEST_THRESHOLD
NUMBER
Minimum size of a request which signals an ORA-4031 error without flushing objects

ABORTED_REQUESTS
NUMBER
Number of requests that signalled an ORA-4031 error without flushing objects

LAST_ABORTED_SIZE
NUMBER
Last size of the request that returned an ORA-4031 error without flushing objects from the LRU list
-----------------------------------------------------------------------------------------
The other side
If the error caused by Kernel when using the 32 bit applications.You need a program from HP the 'shminfo'

[Jean-Luc Oudart · Accepted solution]

Hi

got the error a few weeks ago.
I had to increase the shared pool size

attached a script to estimate your shared pool size. run it a few times to confirm size is ok.

Rgds,
Jean-Luc

[twang]

The ORA-04031 error is usually due to fragmentation in the library cache or shared pool reserved space. Issuing the following query to see shared pool reserved space:
SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size
FROM v$shared_pool_reserved;

Some error may related to BUGs, most of the ORA-4031 errors can be avoided by applying these patchsets.
For Oracle 8i, upgrade to 8.1.7.4.
For Oracle 9i, upgrade to 9.2.0.1.

[Stan_17]

This issue was discussed very recently on the forum. check this link http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=228067

-
Stan

[Yogeeraj_1]

hi,
*
One simple solution: to avoid 4031's -- use bind variables
*
regards
Yogeeraj

[Indira Aramandla]

Hi Dngaya,

The error that you are getting ORA-4031 is Bug 1397603. And is due to SGA memory leak of PERMANENT memory for buffer handles

Versions confirmed, as being affected is 8.1.7.0 and 8.1.7.1
Symptoms:
· Leak - Memory leak / Growth
· Error may occur
· Shared Pool affected

Despite notes elsewhere to the contrary this bug is actually introduced in 8.1.7 and does NOT affect earlier releases. ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles.

Workaround:
Set this
_db_handles_cached = 0

Fixed:
Fixed in 8.1.7.2 patchset. Onwards and in base release 9.0.1.0.

[T G Manikandan]

you should increase the value of shared_pool_size.

[Yogeeraj_1]

hi again,
-
ORA-04021 meaning is your "shared pool was set too small for what you were trying to do, make it bigger"
-
What is your current size of SGA?
-
regards
Yogeeraj

[Hari Kumar]

ORA-4031 is popular error occured with default Shared pool size, It is usually rectified by double sizing the shared pool and also by increasing the SGA.
If in 8.1.7 it is by the bug and resolved with patch and upgrade.
But ORA-4021 is a different one which occurs
with compiling packages(msg: timeout occured while waiting to lock object...... usually with Forms combination it is found mostly)

Think my friend Yogiraaj's numpad struck up with 2 (4031-->4021),(It can be a Typing error , if its oracle it gives KB-0002 :-) )