11 rows selected.
刚才是直接调整SGA_TARGET,我们再来看一下只调整SGA_MAX_SIZE的情况，先重启数据库，使SGA_MAX_SIZE的值回到272M
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 285212672 bytes Fixed Size 1219016 bytes Variable Size 100664888 bytes Database Buffers 180355072 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 272M sga_target big integer 272M SQL> alter system set sga_max_size=350m scope=spfile;
System altered.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 369098752 bytes Fixed Size 1219496 bytes Variable Size 184550488 bytes Database Buffers 180355072 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 352M sga_target big integer 272M SQL> select name,bytes/1024/1024 "size(MB)",resizeable from v$sgainfo;
NAME size(MB) RES ----------------------------------- ---------- --- Fixed SGA Size 1.16300201 No Redo Buffers 2.8359375 No Buffer Cache Size 172 Yes Shared Pool Size 80 Yes Large Pool Size 4 Yes Java Pool Size 4 Yes Streams Pool Size 8 Yes Granule Size 4 No Maximum SGA Size 352 No Startup overhead in Shared Pool 36 No Free SGA Memory Available 80
注意，仅仅调整SGA_MAX_SIZE后，SGA_TARGET并不会跟着调整，因此各内存 组件的值也不会调整，而这时我们发现Free SGA Memory Available的值为80M，而不再是之前的0了，调整SGA_MAX_SIZE为SGA_TARGET增加了80M的上限，这80就是352-272得到的，相当于做了个预留，告诉SGA，你可以现在只用172M，但如果想增加，可以再多用80M，达到352M。而这个时候只要SGA_TARGET重新设置的值没有超过SGA_MAX_SIZE的值，就可以在不停库的情况下增加SGA大小，这样做是有好处的
最后，我们还可以把SGA_TARGET设置为0，即表示禁用10g的新特性――自动分配内存，这样我们可以对各个内存组件的值进行单独设置，对于特殊的应用场景，有时候也是需要的，因为自动管理只是oracle提供的一种便利行，但并不意味着自动调整就一定万事皆OK，11g的自动内存管理就一度被诟病，这里不讨论
SQL> alter system set sga_target=0;
System altered.
SQL> show parameter sga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 352M sga_target big integer 0 SQL> select name,bytes/1024/1024 "size(MB)",resizeable from v$sgainfo;
NAME size(MB) RES ----------------------------------- ---------- --- Fixed SGA Size 1.16300201 No Redo Buffers 2.8359375 No Buffer Cache Size 172 Yes Shared Pool Size 80 Yes Large Pool Size 4 Yes Java Pool Size 4 Yes Streams Pool Size 8 Yes Granule Size 4 No Maximum SGA Size 352 No Startup overhead in Shared Pool 36 No Free SGA Memory Available 80
设置SGA_TARGET=0以后，已经分配的内存组件的值不会变化，如果不设置新值，那么依旧保持原来的值，除非单独设置，即使从起数据库实例也一样，SGA_MAX_TARGET依然不会再根据SGA_TARGET调整，因为SGA_MAX_TARGET我们是给过它确定的值的
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 369098752 byt