Re: ORA-04031 and shared pool

Hi

You could use

alter session set events 'immediate trace name library_cache level 10';

This will dump the sql from the shared pool

cheers

Pete Finnigan
www.pentest-limited.com

In article <9mgsa2$ie7$1_at_news.online.de>, MT <mtechera_at_wpmc.com> writes
>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
>
>
>
>

-- 
Pete Finnigan
IT Security Consultant
PenTest Limited

Office  01565 830 990
Fax     01565 830 889
Mobile  07974 087 885

pete.finnigan_at_pentest-limited.com

www.pentest-limited.com