SGA resize

How to resize SGA

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

SQL> show sga

SQL> show parameter pool

SQL> show parameter sga_target;

SQL> select * from v$sgainfo;

SELECT name,value/1024/1024/1024 "SGA (GB)" FROM v$sga;

SELECT sum(value)/1024/1024/1024 "TOTAL SGA (GB)" FROM v$sga;

show parameter sga_max_size

NOTE: create pfile from spfile before do the chanege.

alter system set sga_target=72G scope=spfile sid='*';

---

SQL> alter system set sga_max_size=32G scope=spfile;

SQL> alter system set sga_target=32G scope=spfile;

System altered.

!!! PGA_AGGREGATE_TARGET should be lower than PGA_AGGREGATE_LIMIT.

NOTE: Soll den wert kleiner sein als SGA und grosser als pga_aggregate_target

SQL> alter system set pga_aggregate_limit=16G scope=spfile;      

System altered.

NOTE: Soll den wert kleiner sein als pga_aggregate_limit

SQL> alter system set pga_aggregate_target=4G scope=spfile;  

System altered.

restart DB:

----

#usefull link with nice example.

http://www.orafaq.com/wiki/SGA_target