Re: ORA-4031 - shared memory fragmentation

Graham Newton wrote:
>
> We've been experiencing several ORA-04031 errors recently when perfoming the
> most innocuous SQL statements on an Oracle 7.2.3 server (IBM RS/6000 AIX
> 3.2.5).
>
> Nothing we've done so far has solved the problem. Basically all we've tried
> is increasing the shared_pool_size a little.
>
> Has anyone else been experiencing these problems? Does anyone have any ideas
> on how to overcome them?
>
> Graham Newton

One of my client is running the same Oracle 7.2.3 version on Sun Solaris 2.5 and getting the same error.

Here is what you can do:

• Upgrade to Oracle 7.2.4 or 7.3.2.3
  • Increase shared pool size (which you already have)
• Once a day run 'Alter system flush shared_pool;'

The problem is that your shared pool area is fragmented and the next SQL that needs a chunk of space can't get it. This could happen if your application is using cusors and NOT closing them when they are finished.

Good luck.......Mashaallah Ebrahimian/CTC,Inc. mebrahim_at_us.net Received on Thu Apr 24 1997 - 00:00:00 CDT