oracle sga_max_size=0,关于sga_max_size

在9208版本下：

关于sga_max_size,oracle文档中如下解释：

Parameter type

Big integer

Syntax

SGA_MAX_SIZE = integer [K | M | G]

Default value

Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.

Parameter class

Static

Range of values

0 to operating system-dependent

specifies the maximum size of SGA for the lifetime of the instance.

这个参数的值，如果在spfile或pfile中没有设置，那么其默认值将在实例启动时，根据各个pool的大小自动生成，如果之后需要动态增大某个pool的值，可能会失败。

如下测试：

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 105977152

SQL> alter system set db_cache_size=100m;

alter system set db_cache_size=100m

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

因此，如果需要动态增大某个pool的值，就需要手动设置sga_max_size的值，如下：

SQL> alter system set sga_max_size=200m scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area  219223120 bytes

Fixed Size                   451664 bytes

Variable Size             184549376 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

SQL> alter system set db_cache_size=100m;System altered.

同样，下面是在10g下设置sga_max_size时，对操作系统共享内存段的使用情况。

bash-3.00$ ipcs -m

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status

0x7c159aa0 32768      ora10g    640        213909504  15

bash-3.00$ exit

exit

SQL> alter system set sga_max_size=250m scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  264241152 bytes

Fixed Size                  1218892 bytes

Variable Size             167773876 bytes

Database Buffers           92274688 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> !

bash-3.00$ ipcs -m

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status

0x7c159aa0 65536      ora10g    640        268435456  13

可见，sga_max_size的设置大小，决定了数据库启动时操作系统分配共享内存段的大小。