LarsBlog

I'm working on a project currently where all the developers have their own instance of Oracle 10g XE (Express Edition) installed on their workstation. By default, the Oracle instance seems to use about 600MB of RAM, which is probably more than we need just for local development. In order to save some RAM by reducing the amount of memory Oracle will allocate, the following SQL is useful.

This statement will show the SGA (Shared Memory) target size - the amount of memory Oracle will try to keep allocated:

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

This statement will alter the SGA target size, and should immediately change the amount of memory an idle Oracle instance will use:

ALTER SYSTEM SET SGA_TARGET = 160M;

If the above gives you the error:

Error: ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
SQLState:  42000
ErrorCode: 2097
Position: 0

It's likely that you are trying to increase the SGA target size to a value higher than the maximum. In that case, you can try the following:

ALTER SYSTEM SET SGA_MAX_SIZE = 160M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET = 160M SCOPE=SPFILE;

The adding of 'SCOPE=SPFILE' means that the change will take affect after the instance is restarted (Which in Windows can be done by restarting the Oracle service from the Control Panel). The SGA_MAX_SIZE parameter cannot be changed without specifying this - ie it can't be change for a running instance.

For more information, see the following URL:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#ADMIN00207