eMarcel.com

Oracle Database, Fusion Middleware, Linux

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…

(Visited 5,915 times, 12 visits today)

, , ,

Tweet
Share
+1
Share

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close