Multiple Block in Oracle

can we have 8kb and 16kb blcoks in databse.

Oracle 9i supports tablespaces with different block sizes. This allows large tables and indexes to have a larger block size than smaller objects .

You can create tablespaces with block sizes different from the standard database block size, which is specified by the DB_BLOCK_SIZE initialization parameter. This feature lets you transport tablespaces with unlike block sizes between databases.

Use the BLOCKSIZE clause of the CREATE TABLESPACE statement to create a table space with a block size different from the database standard block size. In order for the BLOCKSIZE

Recently, a performance consultant company guided us to maintain indexes on tablespace with block size of 16K, while the tables should be created on tablespaces with block size of 8K. Our database is an 11.2.0.4 enterprise edition. Some of the tables/indexes are very large (more than 1 TB).

create tablespace test_tbs4k datafile '/i6052/ora10g3/oracle/oradata/patricia/test_tbs4k.dbf' size 100M blocksize 4K;

This works really nice, this allows to create separate areas of memory

for different things.

For example big blobs documents, in a 32k

normal tables 8k

indexes 16k, etc. etc.

table full scans and index fast full scans are multiblock IO

the rest are single block IO access paths

You have to set the database memory cache in the init.ora for each

different block size."

alter system set db_4k_cache_size = 60M;

DB_2K_CACHE_SIZE  = 0M

DB_4K_CACHE_SIZE  = 0M

DB_8K_CACHE_SIZE  = 0M

DB_16K_CACHE_SIZE = 0M

DB_32K_CACHE_SIZE = 0M