Today I got an issue from a DBA as they were unable to increase SGA_MAX_SIZE in SQL Oracle – then they were suspecting the issue is from Windows OS (Windows Server 2003).

Below Error they were getting while increase SGA_MAX_SIZE in SQL Oracle

SQL> ALTER SYSTEM SET SGA_MAX_SIZE=1400M;

ALTER SYSTEM SET SGA_MAX_SIZE=1400M                *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

Let me go though you How to increase SGA_MAX_SIZE in SQL Oracle?

You need to login to SQL from CLI

 increase SGA_MAX_SIZE in SQL Oracle

 

Now you need run Show SGA

 increase SGA_MAX_SIZE in SQL Oracle

 

Now you need to show parameter spfile

 increase SGA_MAX_SIZE in SQL Oracle

After that create pfile from spfile;

 increase SGA_MAX_SIZE in SQL Oracle

Now you need to alter system set sga_max_size=20m scope=spfile;

 increase SGA_MAX_SIZE in SQL Oracle

Ok Now shutdown immediate;

 increase SGA_MAX_SIZE in SQL Oracle

Increase the Oracle SGA_MAX_SIZE setting in your pfile that you created from your spfile. Then in SQL*Plus start up the database using that pfile as follows.

 increase SGA_MAX_SIZE in SQL Oracle

 

Then re-create your spfile

 increase SGA_MAX_SIZE in SQL Oracle

newsletter-aik

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!