1

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.

3

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.

  • Hi Balazs Papp, Thanks your reply. So If I didn't specify block size when to create a tablespace. Will this tablespace use the db_block_size? Thanks. – Joe.wang Jun 22 '16 at 7:56
  • @Joe.wang Yes, if you do not specify, then the tablespace uses the database default (db_block_size). – Balazs Papp Jun 22 '16 at 7:58
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.