Oracle Database Memory Structures

The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called theShared Global Area). 

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE). 

The SGA has the following mandatory memory structures:

·        Database Buffer Cache

·        Redo Log Buffer

·        Java Pool

·        Streams Pool

·        Shared Pool – includes two components:

o   Library Cache

o   Data Dictionary Cache

·        Other structures (for example, lock and latch management, statistical data)

Additional optional memory structures in the SGA include:

·        Large Pool

The SHOW SGA SQL command will show you the SGA memory allocations. 

·        This is a recent clip of the SGA for the DBORCL database at SIUE. 

·        In order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).