Skip to content

How to Raise SGA_TARGET or MEMORY_TARGET

  • by

Tuning ASSM or ASM target can never be done within SCOPE=MEMORY or even SCOPE=BOTH, which is not allowed.

SQL> alter system set sga_target=64g scope=both;
alter system set sga_target=64g scope=both
*
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

Did you have ever set SGA_MAX_SIZE? Let's make sure this by inspecting pfile.

SQL> create pfile from spfile;

File created.

If there's no SGA_MAX_SIZE in pfile, you can change it in spfile safely within spfile scope.

SQL> alter system set sga_target=64g scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6.8413E+10 bytes
Fixed Size      2270360 bytes
Variable Size   3087010664 bytes
Database Buffers  6.5230E+10 bytes
Redo Buffers     93585408 bytes
Database mounted.
Database opened.
SQL> show parameter sga_

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
sga_max_size        big integer 64G
sga_target        big integer 64G

If there's SGA_MAX_SIZE set in pfile, you should raise SGA_MAX_SIZE first, then raise SGA_TARGET. Of course, within SCOPE=SPFILE.

Leave a Reply

Your email address will not be published.