Resize SGA_TARGET

posted Sep 10, 2010, 6:22 AM by Sachchida Ojha
Some facts about SGA TARGET Parameters

-SGA_TARGET is dynamic
-Can be increased till SGA_MAX_SIZE
-Can be reduced till some component reaches minimum size
-Change in value of SGA_TARGET affects only automatically sized components

SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_max_size                         big integer 1200M
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_target                           big integer 1000M

So, We can resize it to only 1200M.

If we will try to increase it from 1200M we will get ORA-00823.

SQL> alter system set sga_target=1300m;
alter system set sga_target=1300m *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

If we need to increase the SGA_TARGET to 1300M we must first increase our SGA_MAX_SIZE parameter value.
Please note that SGA_MAX_SIZE is a STATIC paramaeter so we need to bounce the instance.

SQL> alter system set sga_max_size=2G scope=spfile; System altered.
Bounce the Instance.

SQL>show parameter sga_max_size
NAME TYPE VALUE ------------------------------------ ----------- -------- sga_max_size big integer 2G
SQL> alter system set sga_target=1500m; System altered.

Comments