Re: ORA-04031 errors

From: Darren Smith <dsmith_at_nospamming.au.oracle.com>
Date: Fri, 22 Oct 1999 15:46:50 +0800
Message-ID: <3810166A.BE269C7E@nospamming.au.oracle.com>

It sounds like your shared_pool doesnt have enough contiguous space. Its probably fragmented.

People usually get this error while attempting to load a big package or while executing a very large procedure and there is not sufficient contiguous free memory in the SHARED_POOL. This may be due to:

Fragmentation or
Insufficient memory in the shared pool.

If it is due to the former, one needs to flush the shared pool and/or break up

the package or procedure into smaller blocks. If the shared pool is badly fragmented, even using small packages or procedures can result in this error. Flushing the shared pool might not help all the time because it will not flush

the PINNED buffers that are being changed at that time.

Utilize dbms_shared_pool package available with 7.0.13 and higher.

This package allows you to display the sizes of objects in the shared pool,

and mark them for PINNING in the SGA in order to reduce memory fragmentation.

The other option is to increase the size of your shared pool.

Hope this helps..

Kenneth C Stahl wrote:

> I have an Oracle 7.3.4 database running under SunOS 5.6. There is 1G
> physical memory in the machine and the shared memory has been set to 400M.
> The initxxx.ora file has a shared_pool_size of 30000000
>
> The nature of the application using the database is that files containing
> ascii data are parsed and the extracted data is inserted into two tables -
> one containing a parent record and the other containing numerous child
> records.
>
> For some reason that I do not understand, after a day or so of operations,
> we start receiving ORA-4031 errors in the PRO-C program which inserts the
> records.
> The only thing that I have been able to do to keep this from happening has
> been to create a cron job which runs periodically to perform a "ALTER
> SYSTEM FLUSH SHARED_POOL".
>
> Does anyone else have any experience with this?

Darren Smith
Oracle Australia

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 22 1999 - 02:46:50 CDT