Oracle Database Check the usage of SGA

SQL> show sga

Total System Global Area 524288000 bytes

Fixed Size                  2046008 bytes

Variable Size             423626696 bytes

Database Buffers           92274688 bytes

Redo Buffers                6340608 bytes

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ -----------
------------------------------
sga_max_size                         big integer 500M

select value/1024/1024 shared_pool_size

from v$parameter

where name = 'shared_pool_size';

select * From v$sgastat;

select 'Memory Allocation' " "

, round(s.sgasize/1024/1024,2) "Total SGA (Mb)"

, round(f.bytes/1024/1024,2) " Free (Mb)"

, round(f.bytes*100/s.sgasize,2) " Free (%)"

from (select sum(bytes) sgasize from sys.v_$sgastat) s

, sys.v_$sgastat f

where f.name = 'free memory'

/

select round(used.bytes /1024/1024 ,2) used_mb

, round(free.bytes /1024/1024 ,2) free_mb

, round(tot.bytes /1024/1024 ,2) total_mb

from (select sum(bytes) bytes

from v$sgastat

where name != 'free memory') used

, (select sum(bytes) bytes

from v$sgastat

where name = 'free memory') free

, (select sum(bytes) bytes

from v$sgastat) tot

/

SQL> select sum(bytes)/1024/1024 " SGA size used in MB" from v$sgastat where name!='free memory';

 SGA size used in MB

          251.23645

Now we will use a simple SQL to see the free memory in SGA.

Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB

  From V$sgastat

  Where Name Like '%free memory%';