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.
