Increase sga_max_size on Oracle 10g Windows

 

1:- Check file location of spfile/pfile

SQL> show parameter spfile;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

2:- Check the value of SGA

    
SQL> show parameter sga_
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 20G
sga_target                           big integer 20G
unified_audit_sga_queue_size         integer     1048576

Increase sga_max_size on Oracle 10g Windows

alter system set sga_max_size=800M scope=spfile;
 
System altered.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             390073072 bytes
Database Buffers          440401920 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL>  alter system set sga_target=800M scope=both;

 

done

 

source : https://dbsguru.com/step-by-step-how-to-increase-sga-size-in-oracle/

https://dba.stackexchange.com/questions/121553/increase-sga-max-size-on-oracle-10g-windows

Check the Usage of SGA 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