Friday , August 18 2017
Home / Oracle DBA / How to change the SGA_TARGET & SGA_MAX_TARGET in Oracle

How to change the SGA_TARGET & SGA_MAX_TARGET in Oracle

 ****  ENABLE SGA_TARGET *****

*Prior to 11g, Oracle manages PGA seperately by the pga_aggregate_target parameter.

*Oracle introduces AMM feature from 11g which will manage SGA as well as PGA.

*When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA and PGA


*You can see in this demo my requirement is to enable 11g feature Memory_Target and give 920m size to SGA.

*I have created database with DBCA and selected SGA size by enabling the feature."Use Automatic Memory Management"

*Oracle created this database with 2gb SGA size, this size oracle calculated 40% from existing RAM which is available in my server.

SGA_TARGET provides the following:
1. Single parameter for total SGA size
2. Automatically sizes SGA components
3. Memory is transferred to where most needed
4. Uses workload information
5. Uses internal advisory predictions
[oracle@dba2 ~]$ export ORACLE_SID=chsr
[oracle@dba2 ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Wed Jul 19 15:48:11 2017

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

ORACLE instance started.
Total System Global Area 376635392 bytes
Fixed Size 1336652 bytes
Variable Size 234883764 bytes
Database Buffers 134217728 bytes
Redo Buffers 6197248 bytes
Database mounted.
Database opened.
SQL>show parameter sga_target;

NAME              TYPE           VALUE
------          -----------    -------
sga_target       big integer      0

SQL>show parameter sga_max_size

NAME               TYPE        VALUE
------------    ----------- -  -----------------------
sga_max_size   big integer    360M

==>>As we can see our automatic SGA tuning is not enabled so we can enable it by setting the ”’SGA_TARGET”’ parameter value.

SQL>alter system set sga_target=160m;
System altered.
SQL>show parameter sga_target;

NAME          TYPE       VALUE
--------    -----------  ----------
sga_target  big integer   160M

*We can resize it to only 360 m if we will try to increase it from 400 m we will get error.

system sga_target=400m 
ERROR at line:ORA-02097:parameter cannot be modified because specified value is invalid 
ORA-00823: Specified value of sga_target greater than sga_max_size
SQL>show parameter spfile;
 NAME     TYPE            VALUE
 spfile  string        /u01/app/oracle/product/11.2.0 /dbhome_1/dbs/spfilehrd.ora

SQL>select sum(value) /1024/1024/1024 TOTAL SGA(GB)from v$sga;
TOTAL SGA(GB).155891418
SQL>show parameter memory 
NAME                   TYPE           VALUE
shared_memory_address   integer        
memory_max_target       big integer 
memory_target           big integer 
shared_memory_address   integer

SQL>show parameter sga  
NAME          TYPE          VALUE 
lock_ sga     boolean        FALSE 
sga_max_size  big integer    160 M 
sga_target    big integer    160 M 

*If you are using a server parameter file (SPFILE),
the database remembers the sizes of the automatically tuned SGA components across instance shutdowns.

SQL>alter system set sga_max_size=524m scope=spfile;

System altered.

SQL>alter system set memory_max_target= 920m scope=spfile;

System altered.

SQL>alter system set memory_max_target=920m scope=spfile;

System altered.

===>>To effect this SGA size you need to bounce the database.

SQL>shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;


ORACLE instance started.
Total System Global Area 548216832 bytes
Fixed Size 1337804 bytes
Variable Size 461375028 bytes
Database Buffers 79691776 bytes
Redo Buffers 5812224 bytes



Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *