Create Oracle tablespaces using multiple DB block sizes.

Summary

Instructions provided describe how to create Oracle tablespaces using multiple DB blocks.

In some cases, it is necessary to restore Oracle databases using exactly the same settings as the original ones. The DB block size is one of the Oracle system parameters. The default DB block size cannot be changed once the database is created, but multiple DB block sizes can be set up to meet the requirement.

Procedure

• Every database has a 'standard' block size specified by db_block_size.
• The SYSTEM and temporary tablespaces use the standard block size.
• Application tablespaces can use other non-standard block sizes.
• All partitions of a table or index must use the same block size.
• The SGA (System Global Area) has a separate buffer cache for each block size:

1. Check current available SGA and buffer size.

   SQL> show sga
   
   Total System Global Area  419430400 bytes
   Fixed Size                  2073288 bytes
   Variable Size             251661624 bytes
   Database Buffers          159383552 bytes
   Redo Buffers                6311936 bytes
   SQL> select name, block_size, current_size from v$buffer_pool;
   
   NAME                 BLOCK_SIZE CURRENT_SIZE
   -------------------- ---------- ------------
   DEFAULT                    8192          152
   

2. Try creating a tablespace with block size = 4kb, this fails with following error.

   SQL> create tablespace test_tbs4k datafile '/i6052/ora10g3/oracle/oradata/patricia/test_tbs4k.dbf' size 10M blocksize 4K;
   create tablespace test_tbs4k datafile '/i6052/ora10g3/oracle/oradata/patricia/test_tbs4k.dbf' size 10M blocksize 4K
   *
   ERROR at line 1:
   ORA-29339: tablespace block size 4096 does not match configured block sizes

3. Alter system to add the db_4k_cache_size parameter.

   SQL> alter system set db_4k_cache_size = 60M;
   
   System altered.
   

4. A new tablespace can now be created using desired block size.

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

5. Double-check the SGA and buffer usage.

   SQL> select name, block_size, current_size from v$buffer_pool;
   
   NAME                 BLOCK_SIZE CURRENT_SIZE
   -------------------- ---------- ------------
   DEFAULT                    8192           92
   DEFAULT                    4096           60