Saturday 4 January 2014

How to increase the size of SGA ?.

+++++++++++++++++++++++++++++++++++++
Please increase the of SGA 3G TO 7G
+++++++++++++++++++++++++++++++++++++
SQL> startup
ORACLE instance started.
Total System Global Area 3207790592 bytes
Fixed Size 2162600 bytes
Variable Size 2785017944 bytes
Database Buffers 402653184 bytes
Redo Buffers 17956864 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 3G
sga_target big integer 0
SQL>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Put below commands in a file with .sql extension and run it.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
vi sgaset7gb.sql
alter system set java_pool_size=512M scope=spfile;
alter system set large_pool_size=200M scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set sga_max_size=0 scope=spfile;
alter system set db_cache_size=2G scope=spfile;
alter system set shared_pool_reserved_size=500M scope=spfile;
alter system set shared_pool_size=4000M scope=spfile;
alter system set streams_pool_size=208M scope=spfile;
alter system set pga_aggregate_target=2G scope=spfile;

SQL> @sgaset7gb.sql
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
SQL>

+++++++++++++++++++++++++
Now bounce the database.
+++++++++++++++++++++++++
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 7334707200 bytes
Fixed Size 2172384 bytes
Variable Size 5167389216 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17661952 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 7024M
sga_target big integer 0
SQL>













No comments:

Post a Comment