OracleDB‎ > ‎Tuning‎ > ‎

Memory Allocation

First the total allocation of memory is limited by sga_max_size and it has to be modified at spfile level so requires a db restart. To look at sga_max_size

show parameter sga_max_size;

to chane it

alter system set sga_max_size=2G scope=spfile;

Then all memory is getting allocated from sga_target the size of it can be set during runtime (assuming it is not larger than sga_max_size). to look at sga_target

show parameter sga_target

to change:

alter system set sga_target=2G scope=both;

look at current allocation status live

select current_size from v$buffer_pool;

select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;