Oracle November 18, 2009 0

How to increase SGA_MAX_SIZE

CASE: Cannot change sga_max_size

ORA02095: specified initialization parameter cannot be modified (Cause: The specified initialization parameter is not modifiable)

Simulation:

SQL> SHOW parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga BOOLEAN FALSE
pre_page_sga BOOLEAN FALSE
sga_max_size big integer 592M
sga_target big integer 0

SQL> ALTER system SET sga_max_size=600M;
ALTER system SET sga_max_size=600M
 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

 

Remedy (specify spfile in alter system statement):

SQL> SHOW parameter spfile
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
 /dbs/spfileDB1.ora
 
SQL> CREATE pfile FROM spfile;
 
File created.
 
SQL> ALTER system SET sga_max_size=600M scope=spfile;
 
System altered.
 
SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup

Should be fine from now on…