MODIFY DB_BLOCK_SIZE in Oracle and create Tablespace with non standard block size.
The basic unit of disk I/O performed by Oracle is an Oracle Block.
Each Oracle block is a combination of several OS blocks and the number of OS block per Oracle block is OS dependent.
The default size of oracle block is specified by the parameter DB_BLOCK_SIZE which is non modifiable during the life time of the database.
Critical tablespaces such as SYSTEM and SYSAUX will be created with the block size specified by the parameter DB_BLOCK_SIZE.
Possible value for 64 bit , the parameter value can be set as 2K, 4K, 8K, 16K and 32K,
for 32 bit servers the maximum value for this parameter is 16K.
OLTP system typically benefit better by using a 4K or 8K blocks, but need to be decided after studying the data model of the database.
DSS databases would benefit better with the block sizes of 16K or 32K.
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
This parameter cannot be changed after creation of the database.
You will get error like below
SQL>
SQL> alter system set db_block_size=16384 scope=spfile;
alter system set db_block_size=16384 scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-32016: parameter db_block_size cannot be updated in SPFILE
SQL> alter system set db_block_size=16384 scope=memory;
alter system set db_block_size=16384 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
If there is a need to create table with non-default block size, it is possible to create it in the same database.
Oracle database allows upto 4 non standard block size tablespaces.
Before creating a tablespace with non standard block size be sure to allocate new buffer cache space that can accommodate blocks with this size by setting DB_nK_CACHE_SIZE.
For example if the default block size is 8K and you want to create a tablespace with 16K blocks then you need to allocate buffer cache to store 16K blocks using the parameter db_16K_cache_size.
You can follow below steps to create a new tablespace with non-default block size without allocating buffer cache with this block size.
SQL> create tablespace 16K_TBS1 datafile '/u01/16k_TBS/16k_tbs1_datafile01.dbf' size 1g blocksize 16k;
create tablespace tbs_16k datafile '/u01/16k_TBS/16k_tbs1_datafile01.dbf' size 1g blocksize 16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
Let’s set the 16K buffer cache as 50M and try to create the tablespace again
SQL> alter system set db_16k_cache_size=50M scope=both;
System altered.
SQL> create tablespace 16k_TBS1 datafile '/u01/16k_TBS/16k_tbs1_datafile01.dbf' size 1g blocksize 16k;
Tablespace created.
Tablespace has been successfully created,
If you want to change the block size of a certain table you need to create a new tablespace with new required block size and move that table to this newly created tablespace.
