How to increase SGA_MAX_SIZE
CASE: Cannot change sga_max_size
ORA–02095: 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…
