Changing SGA and PGA sizes

Oracle instance is made up of background processes and the shared memory (SGA). The background processes perform the maintenance tasks that are required to keep the database running. These background processes operate on the allocated shared memory. For example, one of the background process (PMON) is responsible for cleaning up (releasing locks and resources) after abnormally terminated database connections.

There are two types of memory that Oracle instance allocates:

Oracle Database XE uses Automatic Memory Management (AMM) . This means Oracle Database XE dynamically manages SGA and PGA memories. AMM uses the MAX_TARGET initialization parameters to manage the instance memory. Oracle instance dynamically redistributes the memory between SGA and PGA based on the memory requirements when operating in the AMM mode.

Individual sizes of SGA and PGA can be set using the SGA_TARGET and PGA_AGGREGATE_TARGET parameters respectively. The maximum amount of memory that Oracle Database 11g XE allows for MEMORY_TARGET (SGA and PGA together) is 1 GB.

To change AMM memory, and SGA and PGA parameters, set MEMORY_TARGET to 1 GB as shown in the following command:

SQL> ALTER SYSTEM SET memory_target = 1G SCOPE=spfile; System altered. SQL>