Get rid of ORA-04031 "cannot allocate XXXX Bytes ..."

Today a customer called telling me he was unable to use his software, because at login he receivces an
ORA-04031 "cannot allocate XXX Bytes ..."

First mind "aah out of shared pool".

First i tried an
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

But this doesn't solve the problem.

So i decided to dive into the database and take a look what's going on.

The following query determines the available memory for SHARED_POOL_SIZE in Oracle SGA

SQL> select sum (bytes)/1024/1024 from v$sgastat where pool=’shared pool’;

The above query provides the shared pool size in mega bytes.

The following query determines the total used memory by shared_pool in Oracle SGA.

SQL> select sum (bytes)/1024/1024 from v$sgastat where pool=’shared pool’ and name not in (’free memory’);

The total allocated memory for shared_pool in the init.ora or spfile can be detemined by

SQL> select value from v$parameter where name=’shared_pool_size’


I found a customer package consuming 400 megs of shared pool, i stopped the execution by killing the session and the problem was gone.