增加資料庫SGA的方法

資料庫版本10.2.0.1.

1.如果資料庫庫使用的是pfile
修改pfile檔案,記住sga_max_size的值必須大於sga_target的值.
修改完成後重啟資料庫.

*.sga_max_size=251M
*.sga_target=242M

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1218892 bytes
Variable Size 96470708 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 252M
sga_target big integer 244M

2.如果資料庫使用的是spfile,由於sga_max_size和sga_target的值需要重啟資料庫才能生效.
所以需要登陸資料庫
alter system set sga_max_size=287M scope=spfile;
alter system set sga_target=285M scope=spfile;

然後重啟資料庫即可生效

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 252M
sga_target big integer 244M
SQL>
SQL>
SQL> alter system set sga_max_size=280M;
alter system set sga_max_size=280M
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_max_size=280M scope=spfile;

System altered.

SQL> alter system set sga_target=250M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 1219064 bytes
Variable Size 121636360 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 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 280M
sga_target big integer 252M
SQL>

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-1041037/，如需轉載，請註明出處，否則將追究法律責任。