APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
GOAL:
This note helps in how to use multiple cache size for tablespaces with different block size i.e DB_nK_CACHE_SIZE
Upgrade Gather Stats or running any SQL fails with ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K or 32K errors
SQL> exec dbms_stats.gather_table_stats('CORECAS','LOS_APP_APPLICATIONS',estimate_percent=>100, cascade=>true);
BEGIN dbms_stats.gather_table_stats('CORECAS','LOS_APP_APPLICATIONS',estimate_percent=>100, cascade=>true); END;
*
ERROR at line 1:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K
ORA-06512: at "SYS.DBMS_STATS", line 23938
ORA-06512: at "SYS.DBMS_STATS", line 23989
ORA-06512: at line 1
SOLUTION:
There has been no memory allocated to 8K or 16K or 32K block buffers cache. Explicitly allocating memory to the non-default block buffers will resolve the ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K or 16K or 32K errors
To resolve the issue execute the following:
set DB_nK_CACHE_SIZE to a certain value and restart the instance ( Replace the n with the failing block size 8K or 16K or 32K as appropriate )
For example:
SQL> alter system set DB_16K_CACHE_SIZE=100M scope = spfile;
restart the instance
Recent Posts
