Check SGA total size and free space usage in Oracle
Check the Usage of SGA
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 ;
USED_MB FREE_MB TOTAL_MB
---------- ---------- ----------
799.69 736.3 1536
Find the Total Size of SGA
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
TOTAL SGA (MB)
--------------
1535.99715
Check size of different pool in SGA
Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB From V$sgastat Where Name Like '%free memory%';
POOL FREE_MEMORY_IN_MB
-------------- -----------------
shared pool 564
large pool 108
java pool 32
streams pool 32