ORA-04031: Unable to allocate %s bytes of shared memory

ORA-04031: Unable to allocate %s bytes of shared memory

 

Solutions:

 

alter system set shared_pool_size=100M; -- size you have to decide based

alter system set large_pool_size=100M;   -- on sga_max_size

 

Oracle limits the increase of size up to SGA_MAX_SIZE parameter defined in the initialization parameter file. SGA memory can not be increase beyond SGA_MAX_SIZE. If SGA_MAX_SIZE parameter is not enough for increasing the memory of dynamic parameters, you will get a ORA-00384 error. In this case either you have to increase the SGA_MAX_SIZE parameter or decrease the memory of dynamic parameter.

 

alter system set db_cache_size=135m;

 

alter system set db_cache_size=135m

 

*

 

ERROR at line 1:

 

ORA-02097: parameter cannot be modified because specified value is invalid

 

ORA-00384: Insufficient memory to grow cache

 

 

alter system set  SGA_MAX_SIZE=150M scope=spfile;

 

System altered.

 

The above command change the  limit of max size  to 150M. You have to shutdown and restart the system to make this effect. As of Oracle 9.0.1.1.1, people mostly get ORA-03113: end-of-file on communication channel, when they  startup the database after shutdown immediate. The best solution for this is to exit from sqlplus and login again.

 

The following SQL query will help you to calculate the approximate size of SGA:

 

select SUM(VALUE)+(1024*1024) from v$parameter where name in (

 'db_16k_cache_size','db_2k_cache_size','db_32k_cache_size',

  'db_4k_cache_size','db_8k_cache_size','db_cache_size',

  'db_keep_cache_size','db_recycle_cache_size',

  'java_pool_size','large_pool_size',

  'shared_pool_size','log_buffer');

 

SUM(VALUE)+(1024*1024)

----------------------

             156762112

 

show parameter cache

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 0

db_32k_cache_size                    big integer 0

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 0

db_cache_advice                      string      OFF

db_cache_size                        big integer 33554432

 

DB_CACHE_SIZE is a replacement of DB_BLOCK_BUFFERS in the older version of oracle 8i and before. The other parameters db_8k_cache_size, db_4k_cache_size, db_32k_cache_size, db_2k_cache_size, db_16k_cache_size have the initial default value "0". You can change values of these parameters dynamically.

 

To configure, the multiple block size feature,  alter the system as : -

 

Syntax :- ALTER SYSTEM SET <PARAMETER NAME> = < NEW SIZE> [M|K]

 

Let us say your block size is 4K and you want to configure database for creating a tablespace of block size 4K and 8K respectively.

 

For tablespace of blocksize 4K, alter the database as

 

ALTER SYSTEM SET DB_2K_CACHE_SIZE = 4M;

 

System altered.

 

For Tablespace of blocksize 8K, alter the database as

 

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 8M;

 

System altered.

 

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 50M;

 

System altered.