ORA-04031: unable to allocate xxxxx bytes of shared memory

 
The error means your database has run out of memory. The parameters following this error will tell you which part of the SGA has ran out of memory.

Example of error:
ORA-04031: unable to allocate 27160 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","session param values")

This shows that the shared pool has ran out of memory.

We can double check SGA usage using the query below (check if memory has been freed up or not):

select name,
round(sum(mb),1) mb,
round(sum(inuse),1) inuse,
round(sum(free),1) free
from (select case when name = 'buffer_cache'
then 'db_cache_size'
when name = 'log_buffer'
then 'log_buffer'
else pool
end name,
bytes/1024/1024 mb,
case when name <> 'free memory'
then bytes/1024/1024
end inuse,
case when name = 'free memory'
then bytes/1024/1024
end free
from v$sgastat
)
group by name;

If memory has not yet freed up, you may opt to add more memory to your database.
You may check out this post on how to add more memory to the database.
http://dba.watimbox.com/2012/04/03/resizing-sga/

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart