shared pool error, ora 4031

[Dave Chamberlin]

When trying to compile an oracle package, I am getting
the following error:

ERROR at line 1:
ORA-04031: unable to allocate 122232 bytes of shared memory ["unknown
object","PL/SQL MPCODE","BAMINA: Bam Buffer"

(Oracle 7.3.4, K460x4 2G RAM, HPUX 10.20)

I am not sure what to do to get around this error. The SGA is much larger
that on my development box, yet it compiles ok there. Is the problem
that my shared pool is highly fragmented and cannot allocate enough
contiguous space? If that is so, shouldn't there be some time when
there is enough space? Do I need to increase the SGA (currently 179M)?

On another note - the ITRC forum pages are not displaying correctly. I
have not changed any settings..and am running Netscape 4.?.
Most of the headings are missing, and all input boxes (like for this posting)
are less than 1 character wide (Netscape). On IE5.5, I cannot even put
the focus in the box to post a message(although it actually displays one).
On either browser, when I try to go to the ITRC feedback page, It reads ...
write your comments..., but there are no entry boxes in which to put them!
Has anyone else had any problems?

[Joseph C. Denman]

compare your init files between the two?

Specifically
shared_pool_reserved_size
shared_pool_size
large_pool_size

This may be your prob???


...jcd...

[Rita C Workman]

Here's what Oracle says is the problem and resolution, which goes along where Joseph is pointing:

ORA-04031: unable to allocate num bytes of shared memory num, num, num

Cause: More shared memory is needed than was allocated in the operating system process. SGA private memory has been exhausted.

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 set for the SHARED_POOL_SIZE initialization parameter.

/rcw

[Jared Westgate_1 · Accepted solution]

I agree. It sounds like you don't have enough room in your shared pool. When you get the ORA-04031 error, it means that Oracle has already tried to flush out all of the objects that are not needed, and still didn't have enough room.

If you have the parameter shared_pool_reserved_size set in your init.ora, there is a table that you may find helpful. Look at the V$SHARED_POOL_RESERVED table. The column MAX_FREE_SIZE shows you the largest chunk of memory available in the shared pool.

The reason this package compiles on your developement system, but not the production, probably has more to do with the user load on the database than with the size of the SGA. If you have a busy database, there will be many more objects stored in the shared pool. I would suggest that you look at increasing the size of your SGA (shared pool in particular). I don't know anything about your configuration, but 179 meg SGA sounds real small to me.

I hope this has been a help,

Jared

[Dave Chamberlin]

Thanks for the replies. One question though - if I increase the shared pool size, where is it coming from? Wouldn't I need to have consistently unused RAM to do this?

[Alexander M. Ermes]

Hi there.
How many users are working concurrently on your machine ? What mode do they use ( GUI or char / terminal ) ? Every user process bites of some memory. The amount should be shown in the Oracle Install and Upgrade Guide for this database. And be careful. Ther overall size of SGA for all databases together should not be larger than 1.75 GB ( 32-bit system ).
Try to find out, how much free memory you have on your machine and then start to count, how much you can extend the SGA.
Rgds
Alexander M. Ermes

[Steve Slade]

Dave,

It sounds like your production machine is using MTS. With Oracle 7.3.4, a user's memory allocation is taken from the Shared Pool.

This might not be the case for the development machine, and so when you connect to the database you have a dedicated process, and the process can freely request memory from the operating system, up to the limit set by kernel parameter maxDSize - I think that is parm - a bit rusty at the moment.

With MTS, every user process (PGA) is taken from within the SGA - more users, the more memory that could be potentially be taken. With Oracle 8.X you can allocate space in a seperate part of the SGA from the shared pool, called the Large Pool.