ORA-04031 and shared pool

From: MT <mtechera_at_wpmc.com>
Date: Tue, 28 Aug 2001 21:46:24 +0200
Message-ID: <9mgsa2$ie7$1@news.online.de>

Hi Everyone:

I'm working on 8.1.7. I have a SHARED_POOL of 75 MB (my SHARED_POOL_RESERVED_SIZE = 7.5 MB) and am still getting that dreaded ORA-04031. This happens although I can see that the free memory in the shared pool is over 20%.

I have tried as an emergency measure to use:

ALTER SYSTEM FLUSH SHARED_POOL; but this does not reduce the shared memory usage.

Does anyone know why the shared pool may refuse to flush? I have tried this on an identical system and the shared pool usage drops immediately.

Does anyone know why, when the shared pool still has over 20% free space, Oracle gives 04031? I though that it used an LRU algorithm. If this is the case, why aren't older queries simply being aged out?

I am also now in the hunt for failure to use bind variables. Does anyone have any good scripts or links to scripts that can help me pull out everything in the shared pool for analysis?

Any help and ideas are welcome.

Regards to all,
Mario Received on Tue Aug 28 2001 - 14:46:24 CDT