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.
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
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.
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.