Different Block Size of ORACLE

Different Block Size of ORACLE 

Oracle 9i supports tablespaces with different block sizes. This allows large tables and indexes to have a larger block size than smaller objects. It also means that tablespaces can be transported between databases with differing block sizes. The database is created with a standard block size and up to 5 none-standard block sizes.

The DB_BLOCK_SIZE is used to specify the standard block size which is used for the SYSTEM and TEMPORARY tablespaces. All subsequent tablespaces will also be assigned this block size unless specified otherwise.

The DB_CACHE_SIZE parameter, rather than DB_BLOCK_BUFFERS, is used to define the size of the buffer cache for the standard block size.

This can be set to any size, but it will be rounded to the nearest whole granule.

If the total SGA is less then 128M the granule size is 4M, greater than 128M and it becomes 16M.

The DB_CACHE_SIZE must be at least 1 granule in size and defaults to 48M.

An individual buffer cache must be defined for each non-standard block size used.

The list of five block sizes in Oracle :

2k,4k,8k,16k,32k .

The parameters to set different block sizes :

DB_2K_CACHE_SIZE 
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE 
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE 

To check parameter values we have show command :

To check default block size value

Now the following example shows whole the process of setting up a 2K buffer cache

We need to bounce the database .

Now check the parameter of 2k

Now create tablespace with 2k block size tablespace.

Use DBA_TABLESPACES dictionary view to check blocksize of tablespaces.

In the same way we can change the parameters for 4K,16K,32K block sizes ..

 

Thank you …

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment