Oracle allows the use of Multiple Block Sizes in the database, but each block size must have a corresponding pool in the Database Buffer Cache with the same size.

For example, you cannot cache an 8KB block in a 4KB buffer.

The Block Size in a database can be one of the following: 2KB, 4KB, 8KB, 16KB, or 32KB. However, the Standard Block Size is selected during database creation and cannot be changed afterward. If it is unsuitable, you must create a new database with a more appropriate block size. The block size for the database is defined using the DB_BLOCK_SIZE parameter, and by default, it is 2KB. This parameter is used for formatting SYSTEM and SYSAUX tablespace datafiles.

After creating the database, you can format tablespaces with other block sizes, and the corresponding pool in the Database Buffer Cache is prepared with the same block size.

To configure these pools, you must set one or more of the following parameters:

• DB_2K_CACHE_SIZE
• DB_4K_CACHE_SIZE
• DB_8K_CACHE_SIZE
• DB_16K_CACHE_SIZE
• DB_32K_CACHE_SIZE

Note that you cannot configure the DB_nK_CACHE_SIZE parameter if n represents the standard block size. The standard block size is configured through the DB_CACHE_SIZE parameter.

Here’s a scenario: Let’s assume you have a database with a standard block size of 8KB, which is often the most suitable choice. However, suppose you need to store audio files in the database, and naturally, a block size of 32KB might be more suitable for storing BLOBs. In such a case, you can create a tablespace with a block size of 32KB and then store the LOB Segments in it.

One of the main advantages of using Multiple Block Sizes is the ability to move tablespaces between databases that work with different block sizes. For example, if you have a database with a 4KB standard block size and you want to move it to a database with an 8KB standard block size, you only need to configure the DB_4K_CACHE_SIZE parameter on the second database to accommodate the tablespace, and a new buffer pool will be created for that block size.

It’s important to note that Nonstandard Block Sizes cannot be used in the Temporary Tablespace, but they can be used in the Undo and User Tablespaces. Additionally, nonstandard block sizes cannot be assigned to the Keep or Recycle Pools, but can only be used in the Default Pool.

Now, if you try to create a tablespace with a non-standard block size, the operation will fail with error (ORA-29339) because the buffer cache pool works with the standard buffer.

You must now configure the DB_16K_CACHE_SIZE parameter to create a buffer pool for the new block size, but the total memory size for the SGA cannot exceed the value set in the SGA_MAX_SIZE parameter. If the total size of the SGA reaches its maximum value, you must first reduce the size of one of the SGA memory components or modify the database to close it.