当向日葵向着太阳微笑时...

Symptoms

Dynamically changing DB_CACHE_SIZE fails with ORA-02097 and ORA-00384 errors.

SQL> alter system set db_cache_size=250m;
alter system set db_cache_size=250m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

Cause

There is  no  SGA_MAX_SIZE parameter set explicitly in init.ora and hence its default value at the time of startup is set to sum of the total memory parameters plus some overhead.

SGA given by show sga is same as SGA_MAX_SIZE --> 1142918704

SQL> show sga
Total System Global Area 1142918704 bytes
Fixed Size 732720 bytes
Variable Size 1006632960 bytes
Database Buffers 134217728 bytes
Redo Buffers 1335296 bytes


SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1142918704
SQL> exit

So, if you so a alter system to increase the DB_CACHE_SIZE , this will fail as it cannot grow beyond SGA_MAX_SIZE . Thus, the errors which your are seeing is expected.

Solution

As SGA_MAX_SIZE specifies the maximum size of SGA for the lifetime of the instance, please set SGA_MAX_SIZ to some higher value - ( higher than sum of current sga memory parameters ) to which you expect the memory growth of this instance.

As SGA_MAX_SIZE is a static parameter, you need to set it and re-start the instance once. Thereafter you can  dynamically alter your memory parameters - SHARED_POOL_SIZE, DB_CACHE_SIZE etc to higher values but not exceeding beyond SGA_MAX_SIZE as defined in init.ora.