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.
