English

How To: 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

Since Oracle 9i, databases can now have multiple block sizes.

  • 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:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Use the following workflow to change the buffer cache size to use multiple DB block sizes.
  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