ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K 16K or 32K errors

ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K 16K or 32K errors

Error:

Upgrade Gather Stats or running any SQL  or RMAN restore 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

If you like please follow and comment