Oracle Tips by Burleson Consulting
Changing dynamic SGA and PGA
As we have noted, Oracle provides alter system commands to allow
the DBA to change the configuration of the Oracle RAM memory.
Before we look at using these features for automatic tuning, let’s
briefly review the main parameters and see how they operate. The
output below is an example of a change which has been rejected by
Oracle because there is insufficient UNIX memory to expand the
SQL> alter system set shared_pool_size=64m;
alter system set shared_pool_size=64m
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is
ORA-04033: Insufficient memory to grow pool
In the case of the above example, the error was generated because
the command would have exceeded the value of sga_max_size parameter.
In the example below, we expand the shared pool to 300 megabytes,
and se can immediately confirm the change with the show parameters
SQL> connect system/manager as sysdba;
SQL> alter system set shared_pool_size=300m;
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- -----------
shared_pool_reserved_size big integer 1258291
shared_pool_size big integer 33554432
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.