Re: ORA-04031: shared pool buffer

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Tue, 09 Mar 1999 09:48:59 -0800
Message-ID: <36E55F0B.93525DCD@us.oracle.com>

Martin

The cause of the 4031 error is often memory fragmentation. Have a look at the documentation for the dbms_shared_pool package. There is an Oracle Supplied Packages manual in 8i, I don't have an 8.0 install to check for its equivalent there but if all else fails look at the file that creates it (dbmspool.sql). It will show you how to determine the sizes of the objects you have. You can then create a script that will pin your larger objects, and maybe get away from the problem.

How big should your shared pool be? Can't answer without knowing a lot more about your application. Start out by pinning the bigger packages when the database starts up, then see if you still get the problem. If you do, you need more memory allocated to the shared pool.

HTH. Pete

Martin Terpstra wrote:

> hello,
>
> I got the following problem:
>
> ORA-04031:
> unable to allocate 64528 bytes of shared memory
>
> Oracle says about this message:
>
> 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\")"
> // *Cause: More shared memory is needed than was allocated in the shared
> // pool.
> // *Action: Either use the dbms_shared_pool package to pin large packages,
> // reduce your use of shared memory, or increase the amount of
> // available shared memory by increasing the value of the
> // init.ora parameter "shared_pool_size".
>
> What I like to know is the following. Besides increasing the amount
> of the shared_pool_size value (what is big enough ?),
> 1. what can I do at the moment the problem occurs to make it go away ?
> 2. what can I do to prevent this problem ?
>
> thanks
>
> --
> Martin Terpstra Alcatel Telecom Nederland
> Software Design Engineer Engineering & Development
> phone : +31 (0)70 307-9407 Burgemeester Elsenlaan 170
> fax : +31 (0)70 307-9191 2288 BH RIJSWIJK
> mailto:Martin.Terpstra_at_alcatel.nl NETHERLANDS

Regards

Pete

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark 2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Tue Mar 09 1999 - 11:48:59 CST