Block size is for Database or Tablespace?

Question

Forgive me I am just a newbie in the Oracle. I saw there is a db_block_size parameter which can be specified the block size for a database so that the whole database will be read and written data with the specified block size. But what make me confused is we can also specify block size for the Tablespace when we want to Create Tablespace. Please check the Block size clause. My question is are they contradictory? How could they both work in the same Database. Because the TableSpace belongs to a Database. Thanks.

Answer 1

db_block_size is the default block size of the database, but it does not necessarily mean all your tablespaces have the same block size, you can have tablespaces with different block sizes.

You need to define seperate cache areas for tablespaces with non-default block size. For example, if your db_block_size is 8192, and you want to create a tablespace with block size of 16384, first you need to specify a size for the "16K" cache with the parameter db_16k_cache_size, and after that you can create a tablespace with 16384 block size.

Answer 2

Tablespaces with different block sizes than the database exists because of transportable tablespaces, where you want to move the tablespace to a different database that has a different block size than the database you are moving the tablespace from (usually from an OLTP database to a data warehouse).

Although certain sites claim you can get a performance increase with tablespaces of different block sizes from the database, that is an unintended side effect (when it actually works out that way), and it should not be a consideration when designing a database. Please see Ask Tom for more information.