SCRIPTS :

Run the following script :


"ORA-4031 - Unable to allocate bytes of shared memory"

ORA-4031 is a very common error that many dba's face in their day to day activities. This error can commonly occur due to the SHARED POOL SIZE. This error can be due to an inadquate sizing of the SHARED POOL or due to  fragmentation of the shared pool.


- Increase Shared_Pool if too small (and therefore SGA_TARGET)


- _shared_pool_reserved_pct = 10 (default parameter value = 5%,  10% is better and advised by Oracle)
alter system set "_shared_pool_reserved_pct"= 10 scope = spfile;


- begin traces:
alter system set events '4031 trace name errorstack level 3: 4031 trace name HEAPDUMP level 536870914 ';


- Stop traces:
alter system set events '4031 trace name HEAPDUMP off ';


- Trace analysis:
Search query / module has asked for the memory


- Run SGAStat query every 30 minutes to check the evolution of dispatch of memory:

SELECT *
FROM v$sgastat
WHERE name IN ('free memory', 'db_block_buffers', 'log_buffer',
'dictionary cache', 'sql area', 'library cache');