Steps to increase the size of SGA memory in Oracle
- Check the SGA parameter which show the size of SGA allocated in Oracle
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1536M
sga_min_size big integer 0
sga_target big integer 1536M
unified_audit_sga_queue_size integer 1048576
-
Check the memory of your Operating system before increasing the SGA size.
-
Alter the size of SGA as required. I am increasing up to 2G;
SQL> ALTER SYSTEM SET SGA_MAX_SIZE=2G SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET SGA_TARGET=2G SCOPE=SPFILE;
System altered.
Note: It is reommended to keep the SGA MAX SIZE value larger then SGA TARGET for future need, If we need in future than we can increase SGA TARGET without restart the database.
- Restart the database for changes effect.
Shutdown immediate;
Startup;
- Verify the changes are done.
SQL> show parameter sga_