Your SGA has become fragmented ...
Your will have to restart the database to clear up the fragmentation.
Your should attempt to pin some of the objects in memory that
are getting reloaded multiple times... See script below to view
the reloads and sizes of objects...
You also may need to look at resizing your SGA
Another aid is the following undocumented init parameter in 8.1.7
#### Work around for BAMIMA Buffer (4031) errors ####
_db_handles_cached = 0
To determine what large PL/SQL objects are currently loaded in the shared pool
and are not marked 'kept' and therefore may cause a problem, execute the following:
select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
from v$db_object_cache
where sharable_mem > 100
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;
--- Objects that have been reloaded ---
select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
from v$db_object_cache
where loads > 1
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;
In the metalink, you will find a lot of such thread. I picked up from there..
Solution:
Well, still, shared pool is not big enough.
ORA-04031 unable to allocate string bytes of shared memory ("string","string","string","string")
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
Reply With Quote
Bookmarks