Setup Oracle's SGA size

I always work with Oracle databases on my laptop. I prefer to install them myself so I can tune it to run without using all my memory. Also, instead of installing Oracle directly on my laptop I always do so on virtual machines because this way it is easier to share my environments with my colleagues and to setup individual sandboxes for each project I am working on.

When I have to work with databases installed by others I found I usually have to decrease how much memory is assigned to Oracle. The scripts below describe how to do just that.

• update Oracle's SGA size:

-- show current SGA size
select sum(value)/1024/1024 from v$sga;

-- set SGA target size to 256MB
alter system set sga_target = 256M scope=both;

-- list buffer size
select current_size from v$buffer_pool;

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

-- additional Oracle memory settings:
-- make sure you don't setup SGA to the same or higher size than MEMORY
alter system set memory_max_target = 512M scope=spfile;
alter system set memory_target = 512M scope=spfile;
alter system set sga_max_size = 256M scope=spfile;
alter system set sga_target = 256M scope=spfile;
-- you will need to restart Oracle for the changes to take effect (e.g. dbshut and dbstart)