修改oracle+sga+size,oracle自动内存共享管理测试。修改 oracle 11g SGA_MAX

目的，使用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。

########################################### # 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://www.cnblogs.com/qb371/archive/2012/02/08/2366269.html