oracle自動記憶體共用管理測試。修改 oracle 11g SGA_MAX_SIZE。

目的，使用oracle 自動記憶體共用管理 基本都是修改過程。如需整理，請留言

 

SQL>show parameters target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 4612M
sga_target                           big integer 1536M

SQL> show parameter statistics_level;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
statistics_level                     string      TYPICAL（ALL）

 

SQL> show parameter sga_max_size;

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

SQL> show sga;

Total System Global Area 1603411968 bytes
Fixed Size                  2176168 bytes
Variable Size             520096600 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7397376 bytes

 

 

第一步 ：找到pfile檔案

修改想要的大小，注意 貌似win2000 中 sga_max_size不能大於1.7G。。我在server2008上，修改為31G。

否則出錯後 要參考：http://blog.csdn.net/xiaojian/archive/2008/07/29/2732149.aspx

 

###########################################
# Pools   
###########################################
sga_max_size=31000000000

 

SQL> startup pfile='D:/app/Administrator/admin/xxx/pfile/init.ora.1121201018371';
ORACLE 常式已經啟動。

Total System Global Area 3.1868E+10 bytes
Fixed Size                  2191456 bytes
Variable Size            3.0870E+10 bytes
Database Buffers          939524096 bytes
Redo Buffers               56213504 bytes

 

SQL> show sga;

Total System Global Area 3.1868E+10 bytes
Fixed Size                  2191456 bytes
Variable Size            3.0870E+10 bytes
Database Buffers          939524096 bytes
Redo Buffers               56213504 bytes

SQL> select sum(value)/1024/1024 from v$sga;

SUM(VALUE)/1024/1024
--------------------
          30391.7031

 

SQL> show parameters target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 4612M
sga_target                           big integer 1536M

SQL> show parameter sga_max_size;

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

SQL> alter system set sga_target=30000m;

系統已更改。

 

SQL> show parameters target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 4612M
sga_target                           big integer 30016M

http://blog.csdn.net/johnstrive/article/details/6271103