Monday, May 10, 2010

Creation of tablespaces with non standard block size
The standard block size is the size of the default database block size which is specified by the parameter DB_BLOCK_SIZE. The other data block sizes that we create in the database are non standard block size.
When we create db_block_size of 8K or any size, oracle expects memory allocated to DB_CACHE_SIZE parameter. In the same manner, if we wanted to create a non standard block size of ‘n’ then oracle expects the memory size allocated to DB_CACHE_SIZE and DB_Nk_CAHCE_SIZE parameter where n is equal to the ‘n’ in the non standard block size.
Note: For ‘n’ the possible values are 2, 4, 6, 8, 16, 32 etc..

SQL> create tablespace test_ts
2 datafile 'd:\tablespacetest.dbf'
3 blocksize 16k;
create tablespace test_ts
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> alter system set db_16k_cache_size=1M
SQL> /
System altered.

SQL> create tablespace test_ts
2 datafile 'd:\tablespacetest.dbf'
3 size 200k
4* blocksize 16k
SQL> /
Tablespace created.
Note: if we wish to create a non standard block size of 4k then there should be a memory allocated to DB_4K_CACHE_SIZE parameter. This rule applies to all the non standard block sizes.


No comments: