Let us we discuss how to check sga total size & free space in Oracle11g ?

1)Total Size:

* The view (V$sga) display summary information about the system global area (SGA).

 


SQL> desc V$sga
 Name                                    Null?                 Type
 -------------------------            --------              --------------------
 NAME                                                           VARCHAR2(20)
 VALUE                                                          NUMBER

 

Let’s have a short explanation of all column of the v$sga parameter:

*Name: Sga component group

*Value: Memory size (in bytes)

 


SQL> select sum(value)/1024/1024/1024 Total_size_In_GB from V$sga;

TOTAL_SIZE_IN_GB
---------------------------
      .600208282

 

2)Free space:

*The View (V$SGASTAT) displays detailed information on the system global area (SGA).

 




SQL> desc v$sgastat
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------

 POOL                                                           VARCHAR2(12)
 NAME                                                           VARCHAR2(26)
 BYTES                                                          NUMBER

 

Let’s have a short explanation of all column of the v$sgastat parameter:

I)POOL - Designates the pool in which the memory in NAME resides:

*Shared pool = Memory is allocated from the shared pool

*Large pool = Memory is allocated from the large pool

*Java pool = Memory is allocated from the Java pool

*Stream pool = Memory is allocated from the stream pool

II)NAME - SGA component name

III)BYTES - The Memory size in bytes

 

*In detailed view sga free memory space:


 SQL> Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB
  2   From V$sgastat
  3   Where Name Like '%free memory%';

POOL         FREE_MEMORY_IN_MB
------------ --------------------------------
shared pool                189
large pool                  0
java pool                   4
streams pool                8

 

*The Total free space :


SQL> Select sum(bytes/1024/1024) Free_Memory_In_MB
  2   From V$sgastat
  3   Where Name Like '%free memory%';

FREE_MEMORY_IN_MB
--------------------------------
       200.899017