How to reset sga_max_size

Today I decided to increase memory but before that I wanted to change sga_max_size`s value.

Because in my production database configured sga_max_size to 12Gb.

It means independing on busy or idle of instance state, SGA could not be more than 12GB (it is worse).
The SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

Our instance configured AMM:

select name, value from v$parameter where name like 'memory%'  

memory_target    32212254720
memory_max_target    32212254720

--Checking init file

-bash-3.2$ more initPROD.ora| grep -i sga
PROD.__sga_target=12616466432
*.sga_max_size=25769803776
*.sga_target=0
-bash-3.2$

SGA_MAX_SIZE is not dynamic value, after changing those value, you should restart instance.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 12G
sga_target                           big integer 0

SQL> alter system set sga_max_size=0 scope=spfile;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1,2828E+10 bytes
Fixed Size                  2272944 bytes
Variable Size            6710886736 bytes
Database Buffers         6106906624 bytes
Redo Buffers                7602176 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 12G
sga_target                           big integer 0

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.0.1/db_1/dbs/spfilePROD.ora


Uppps! Nothing is changed!

-bash-3.2$ cd /u01/app/oracle/product/11.0.1/db_1/dbs/
-bash-3.2$ more spfilePROD.ora | grep sga
PROD.__sga_target=12276727808
*.sga_max_size=0
*
-bash-3.2$

As you see spfile changed but Oracle set this parameter itself.

After that I investigated from the internet and found interesting post from Tom Kyte.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516

"You are using automatic memory management, we set things - the caches, everything, to whatever we feel like.
If you set memory_target, you need set nothing else. Just don't set it at all. Unset it."


If you want to set sga_max_size turn of memory parameters then set sga_max_size.